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?

Tag your question with the database you are using. – Gordon Linoff Jan 15 '21 at 13:21 — Gordon Linoff, Jan 15 '21 at 13:21

Charlieface · Answer 1 · 2021-01-15T09:43:24.950

You are not grouping by business_name in the inner queries so you cannot select it without some kind of aggregation. Incidentally, your inner group by is unnecessary, so we can just remove it:

select
    COALESCE(Funding_Type, 'Total') as Funding,
    nama as nama1,
    sum(total) as Revenue
from (
    select 'NDIS' as Funding_Type, business_name as nama, 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', business_name, total_amount
        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);

You probably want a COALESCE on nama also, for the subtotal.

EDIT

If you want to have multiple checks on invoice_to, you can simplify the whole query like this:

select
    COALESCE(Funding_Type, 'Total') as Funding,
    business_name as nama1,
    sum(total) as Revenue
from invoices a 
inner join businesses b on a.business_id=b.id
join (values    -- you could also use a table variable or TVP here
    ('NDIS'),
    ('CHSP')
) v (Funding_Type) on b.invoice_to like '%' + v.Funding_Type + '%'
GROUP by ROLLUP (v.Funding_Type, business_name);

Note that you may get doubled up rows if a row matches more than one Funding_Type.

what if we wanted to add a condition example: and b.business_name = 'Marli and Moe'. Do we have to add it in each inner join or can we put it only once? and placed where? `and invoice_to like '%CHSP%' and b.business_name='Marli and Moe' ) x GROUP by ROLLUP (Funding_Type, nama);` — ryan, Jan 15 '21 at 08:26
Thankyou bro, can you help me, I have a case about date and time. https://stackoverflow.com/questions/65712954/how-to-read-from-the-two-same-dates-without-input-the-time — ryan, Jan 18 '21 at 04:54

score 1 · Accepted Answer · answered Jan 15 '21 at 13:20

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);

Thankyou bro, can you help me, I have a case about date and time. https://stackoverflow.com/questions/65712954/how-to-read-from-the-two-same-dates-without-input-the-time — ryan, Jan 18 '21 at 04:52
@ryan . . . When you ask a question and it has been answered satisfactorily, you should accept one -- and exactly one -- answer. — Gordon Linoff, Jan 18 '21 at 16:38

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

2 Answers2