Is this the right way to make summary report using SQL?

I wrote a function to create a summarised report in codeigniter 3 but unable to get desired results. Problem Statement: My case statements are not working properly and when case statement is true the count and value is always equivalent to no of total records and total value.

Q1: Is there any logical error?

Q2: Am I using the right approach?

Q3: Is there any other way to create report using sql queries?

Can anybody help me to fix the issue I will be grateful please.

function summarisedReport(){

$this->db->select('b.code, b.code_name, currency,allocation_in_r, total_allocation,count(*) as total_records,'

. 'case when a.currency_id = 1 then sum(total_value) else sum(total_value_in_us) end as total_consumption,'

. 'case when a.status_id = 1 and a.status_id = 1 and a.status_id = 1 then count(*) else 0 end as no_of_draft_records,'

. 'case when a.status_id = 1 then case when a.currency_id = 1 then sum(total_value) else sum(total_value_in_us) end else 0 end as draft_records_value,'

. 'case when a.status_id = 2 then case when a.currency_id = 1 then sum(total_value) else sum(total_value_in_us) end else 0 end as cfa_conveyed,'

. 'case when a.status_id = 3 then count(*) else 0 end as contracted_records,'

. 'case when a.status_id = 3 then case when a.currency_id = 1 then sum(total_value) else sum(total_value_in_us) end else 0 end as contracted_records_value,'

. 'total_allocation-(allocation_in_r+(case when a.status_id = 2 then case when a.currency_id = 1 then sum(total_value) else sum(total_value_in_us) end else 0 end)) as remaining_allocation');
$this->db->from('table_a a');
$this->db->join('table_b b','a.code_id = b.code_id', 'left outer');
$this->db->join('table_c c','a.code_id = c.code_id and a.fin_year = c.fin_year', 'left outer');
$this->db->where('a.fin_year','2022-23');
$this->db->group_by('b.code, b.code_name, currency,allocation_in_r, total_allocation');
$query = $this->db->get();
return $query->result();
}

Solved As Follows

function summarisedReport(){

$this->db->select('b.code, b.code_name, currency,allocation_in_r, total_allocation,count(*) as total_records,'

. 'sum(case when a.currency_id = 1 then total_value else total_value_in_us end) as total_consumption,'
. 'sum(case when a.status_id = 1 and a.status_id = 1 and a.status_id = 1 then 1 else 0 end) as no_of_draft_records,'
. 'sum(case when a.status_id = 1 then case when a.currency_id = 1 then total_value else total_value_in_us end else 0 end) as draft_records_value,'
. 'sum(case when a.status_id = 2 then case when a.currency_id = 1 then total_value else total_value_in_us end else 0 end) as cfa_conveyed,'
. 'sum(case when a.status_id = 3 then 1 else 0 end) as contracted_records,'
. 'sum(case when a.status_id = 3 then case when a.currency_id = 1 then sum(total_value) else sum(total_value_in_us) end else 0 end as contracted_records_value,'
. 'total_allocation-(allocation_in_r+sum(case when a.status_id = 2 then case when a.currency_id = 1 then total_value else total_value_in_us end else 0 end)) as remaining_allocation');

$this->db->from('table_a a');
$this->db->join('table_b b','a.code_id = b.code_id', 'inner');
$this->db->join('table_c c','a.code_id = c.code_id and a.fin_year = c.fin_year', 'left');
$this->db->where('a.fin_year','2022-23');
$this->db->group_by('b.code, b.code_name, currency,allocation_in_r, total_allocation');
$query = $this->db->get();
return $query->result();
}