'Performing a multiple sum query with a subquery

So I have a query that does multiple sums that works great. Here is a stripped down version.

select sum(Case when PLU='WIDG101' then qty else 0 end) as Widget1,
sum(Case when PLU='WIDG201' then qty else 0 end) as Widget2
from SalesTable

But I have a specific list of PLUs that I want to use and may need to change from time to time. So I declared some tables and populated it like this:

Declare @WidgetTable1 (Widget1s varchar(20))
Declare @WidgetTable2 (Widget2s varchar(20))
Insert into WidgetTable1 Values ('WIDG101'),('WIDG102'),('WIDG103')
Insert into WidgetTable2 Values ('WIDG201'),('WIDG202'),('WIDG203')

select sum(Case when PLU IN (Select * from @WidgetTable1) then qty else 0 end) as Widget1,
sum(Case when PLU IN (Select * from @WidgetTable2) then qty else 0 end) as Widget2
from SalesTable

But I get: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Which I understand but I am stuck on a way around it.

Any Suggestions?

sql


Solution 1:[1]

You can right outer join the tables rather than using a subquery:

    select 
       sum(coalesce(s.qty,0)) Widget1, sum(coalesce(s.qty,0)) Widget2
    from
       SaleTable s
         right outer join @WidgetTable1 w1
           on s.PLU = w1.Widget1s
         right outer join @WidgetTable2 w2
           on s.PLU = w2.Widget2s

Solution 2:[2]

Not an answer but my response was to long.

So I tried it like this:

Declare @WidgetTable1 table (Widget1s varchar(20))
Declare @WidgetTable2 table (Widget2s varchar(20))
declare @Salestable table (PLU varchar(20),qty int)
Insert into @WidgetTable1 Values ('WIDG101'),('WIDG102'),('WIDG103')
Insert into @WidgetTable2 Values ('WIDG201'),('WIDG202'),('WIDG203')
Insert into @Salestable Values ('WIDG101',8),('WIDG202',10),('WIDG103',5)

select 
   sum(coalesce(s.qty,0)) Widget1, sum(coalesce(s.qty,0)) Widget2
from
   @Salestable s
     right outer join @WidgetTable1 w1
       on s.PLU = w1.Widget1s
     right outer join @WidgetTable2 w2
       on s.PLU = w2.Widget2s

It gives both columns as 0. If I only right join the w1 table, the query 'works' in that it only sums the associated PLUs, but still gives identical answers for both columns. So you are definitely on to something. I am still playing with it, but still need some additional help. Thank you so much for trying! Any additional thoughts are welcome.

Solution 3:[3]

As a deadline approached, I ended up running 2 queries and assembling them.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Rick
Solution 3 Rick

Related Questions