Hello Kool Team,

Can you educate me on how to create a new datastore from an existing query without developing a new query?

Can I create a new datastore for the following senario :

__ $this->src('nakdef')

    ->query("
    SELECT a.applicant_id,
        application_code,applicant_name,
        coalesce(applied_community,'Minor communities') as applied_community,
        coalesce(institution_type,'Not specified') as institution_type,
        (total_budget/cast(duration_of_support as numeric)) as total_term_fees,
        total_budget,sum(payment_amount) as paid, total_budget-sum(payment_amount)  as balance
        FROM
        v_applicant a
        inner join nak_applicant_payment b on a.applicant_id=b.applicant_id
        group by application_code,applicant_name,a.applicant_id,
        coalesce(applied_community,'Minor communities'),total_budget,
        coalesce(institution_type,'Not specified'),
        (total_budget/cast(duration_of_support as numeric))
        ")->saveTo($result_raw);
        
        $result_raw->pipe($this->dataStore("result_data"));
   __

I want to create another datastore from the saved $result_raw variable with the following fields and aggregated columns only

applied_community, count(applicant_id) as no of applicants, sum(total_budget) as total_budget_by_community sum(paid) as total_paid_by_community, sum(balance) as total_balance_by_community

Counting on your prompt assistance?

Hello, I tried your suggestion and it did not work. 3 of the new columns are new aggregate columns from the original query. How do I combine OnlyColumn with AggregatedColumns to form the new datastore as I tried below but not working. That is what I want to achieve?

	        //Derived column
			$result_raw->pipe(new OnlyColumn(array
			("applied_community"
			)))
					
			->pipe(new AggregatedColumn(array(	
				"no_of_applicants"=>("count","applicant_id"),
				"total_budget_by_community"=>("sum","total_budget"),
				"total_paid_by_community"=>("sum","paid"),
				"total_balance_by_community"=>("sum","balance"),
				)))
			->pipe($this->dataStore("result_with_aggregate"));