Cannot use an aggregate or a subquery

I’m trying to group this case expression but unfortunately I’m getting an error.

select da.order_id, osl.itemid, sum(case when osl.sku = '00005' then 0 when osl.sku = '00006' then 0 else osl.price * sil.quantity end) merchcost, sum(sil.merchUnitCost * sil.quantity) cogs, sum(sil.quantity) quantity,
    CASE
    WHEN EXISTS (SELECT * FROM fcat.dbo.subscriptionskus a where a.itemId = osl.itemid) then 1
    WHEN EXISTS (SELECT * FROM foam.dbo.subscriptionprogram b where b.orderStateLineId = osl.orderStateLine_id ) then 1
    else 0
    END as isAdmin,

    CASE
    WHEN EXISTS (SELECT * FROM fcat.dbo.subscriptionskus a where a.itemId = osl.itemid) then CAST('subscriptionskus' as varchar(MAX))
    WHEN EXISTS (SELECT * FROM foam.dbo.subscriptionprogram b where b.orderStateLineId = osl.orderStateLine_id ) then CAST('subscriptionprogram' as varchar(MAX))
    else 'NotListed'
    END as SubTable

from #dispatchAmounts da 
inner join orderstates os on os.order_id = da.order_id
inner join orderstatelines osl on osl.orderState_id = os.orderState_id
inner join shippingIntentLines sil on sil.orderStateLine_id = osl.orderStateLine_id and da.shippingIntent_nbr = sil.shippingIntent_nbr
where da.code = 'merch' and sil.quantity > 0
group by da.order_id, osl.itemid, CASE
    WHEN EXISTS (SELECT * FROM fcat.dbo.subscriptionskus a where a.itemId = osl.itemid) then 1
    WHEN EXISTS (SELECT * FROM foam.dbo.subscriptionprogram b where b.orderStateLineId = osl.orderStateLine_id ) then 1
    else 0
    END,CASE
    WHEN EXISTS (SELECT * FROM fcat.dbo.subscriptionskus a where a.itemId = osl.itemid) then CAST('subscriptionskus' as varchar(MAX))
    WHEN EXISTS (SELECT * FROM foam.dbo.subscriptionprogram b where b.orderStateLineId = osl.orderStateLine_id ) then CAST('subscriptionprogram' as varchar(MAX))
    else 'NotListed'
    END

ERROR:

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Is there anyway I can make this work?

Answer

I have a couple of suggestions. You are using select * in your case statements , but the columns referenced by ‘*’ are not in your group by clause. To add them would be a bad way to fix the problem. Instead use ‘ select 1 ‘ and see what happens.

Second recommendation is to just not use the when exists rather use left join using sub queries and use those in your case statements.

Advertisement

Answer