Invalid in the select list because it is not contained in either an aggregate function (with union and rollup)

Question

i have query like below

select COALESCE(Funding_Type, 'Total') as Funding, nama as nama1, sum(total) as Revenue
from (select ('NDIS') as Funding_Type, business_name as nama, sum(total_amount) as total
      from invoices a inner join
           businesses b 
           on a.business_id=b.id and invoice_to like '%NDIS%'
      union ALL
      select ('CHSP') as Funding_Type, business_name as nama, sum(total_amount) as total 
      from invoices a inner join
           businesses b 
           on a.business_id=b.id and invoice_to like '%CHSP%') x
GROUP by ROLLUP (Funding_Type, nama);

And get results like this

Column 'businesses.business_name' is invalid in the select list because it is not contained in either 
an aggregate function or the GROUP BY clause.

Does anyone know how to fix it?

Answer 1

Let me assume that your groups are non-overlapping. I assume this is the case; otherwise, you are double counting revenue which seems like a bad thing.

With this assumption, you can use a single case expression for the aggregation:

select coalesce(Funding_Type, 'Total') as Funding,
         nama as nama1, sum(total) as Revenue
from (select (case when invoice_to like '%NDIS%' then 'NDIS'
                   when invoice_to like '%CHSP%' then 'CHSP'
              end) as Funding_Type,
             business_name as nama, total_amount as total
      from invoices i inner join
           businesses b 
           on i.business_id = b.id
     ) ib
where funding_type is not null
group by rollup (Funding_Type, nama);