Performing a multiple sum query with a subquery

user_11168878 0
Viewed 49

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
3 Answers

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
user_70143750

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.

user_111688780

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

user_111688780
Related