SQL - SUM within subquery

Question

I have the following code that looks at the SalesVol of different products and groups it by transaction_week

SELECT a.transaction_week,
SUM(CASE WHEN record_type IN (6,37,13) THEN quantity ELSE 0 END) as SalesVol
FROM table 1 a 
LEFT JOIN table 2 b ON b.Date = a.transaction_date
LEFT JOIN table 3 c ON c.sku = a.product
WHERE series in (62,236,501,52)
GROUP BY a.transaction_week
ORDER BY a.transaction_week

| tw | SalesVol |
| 1  | 4768 |         
| 2  | 4567 |
| 3  | 4354 |
| 4  | 4678 |

I want to be able to have multiple subqueries where I change the series numbers for example.

SELECT a.transaction_week,

(SELECT SUM(CASE WHEN record_type IN (6,37,13) THEN quantity ELSE 0 END) as SalesVol 
FROM table 1 a 
LEFT JOIN table 2 b ON b.Date = a.transaction_date
LEFT JOIN table 3 c ON c.sku = a.product
WHERE series in (62,236,501,52)) as personal care

(SELECT SUM(CASE WHEN record_type IN (6,37,13) THEN quantity ELSE 0 END) as SalesVol 
FROM table 1 a 
LEFT JOIN table 2 b ON b.Date = a.transaction_date
LEFT JOIN table 3 c ON c.sku = a.product
WHERE series in (37,202,203,456)) as white goods

FROM table 1 a 
LEFT JOIN table 2 b ON b.Date = a.transaction_date
LEFT JOIN table 3 c ON c.sku = a.product
GROUP BY a.transaction_week
ORDER BY a.transaction_week

I can't get the subqueries at work as it is giving me the overall sum value and not grouping it by transaction_week

Which DBMS are you using? Postgres? Oracle?

user330315
– user330315

2017-08-21 12:17:42 +00:00
Commented Aug 21, 2017 at 12:17 — user330315
– user330315, Commented Aug 21, 2017 at 12:17

markusk · Accepted Answer · 2017-08-21 12:26:02Z

1

Instead of using subqueries, add series to the condition of the CASE statements:

SELECT a.transaction_week,
  sum(CASE WHEN series IN (62,236,501,52) AND record_type IN (6,37,13)
         THEN quantity ELSE 0 END) as personal_care, 
  sum(CASE WHEN series IN (37,202,203,456) AND record_type IN (6,37,13)
         THEN quantity ELSE 0 END) as white_goods 
FROM table 1 a 
LEFT JOIN table 2 b ON b.Date = a.transaction_date
LEFT JOIN table 3 c ON c.sku = a.product
GROUP BY a.transaction_week
ORDER BY a.transaction_week;

answered Aug 21, 2017 at 12:26

markusk

6,72636 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Radim Bača · Accepted Answer · 2017-08-21 12:20:26Z

You just miss the a.transaction_week in you subquery. The JOIN in outer query is unneccessary.

SELECT a.transaction_week,
(
  SELECT SUM(CASE WHEN record_type IN (6,37,13) THEN quantity ELSE 0 END) as SalesVol 
  FROM table 1 a2 
  LEFT JOIN table 2 b ON b.Date = a2.transaction_date
  LEFT JOIN table 3 c ON c.sku = a2.product
  WHERE series in (62,236,501,52) AND a2.transaction_week = a.transaction_week
) as personal care,
(
  SELECT SUM(CASE WHEN record_type IN (6,37,13) THEN quantity ELSE 0 END) as SalesVol 
  FROM table 1 a 2
  LEFT JOIN table 2 b ON b.Date = a2.transaction_date
  LEFT JOIN table 3 c ON c.sku = a2.product
  WHERE series in (37,202,203,456)  AND a2.transaction_week = a.transaction_week
) as white goods
FROM table 1 a 
GROUP BY a.transaction_week
ORDER BY a.transaction_week

Abhishek Sharma · Accepted Answer · 2017-08-21 12:29:00Z

Try this it would work fast as well as up to your requirement:

SELECT  a.transaction_week ,
        whitegoods.SalesVol AS 'White Goods' ,
        personalcare.SalesVol1 AS 'Personal Care'
FROM    table1 a
        LEFT JOIN table2 b ON b.[Date] = a.transaction_date
        LEFT JOIN table3 c ON c.sku = a.product
        CROSS APPLY ( SELECT    SUM(CASE WHEN record_type IN ( 6, 37, 13 )
                                         THEN quantity
                                         ELSE 0
                                    END) AS SalesVol
                      FROM      table1 a2
                      WHERE     b.[Date] = a2.transaction_date
                                AND c.sku = a2.product
                                AND series IN ( 37, 202, 203, 456 )
                                AND a2.transaction_week = a.transaction_week
                    ) whitegoods
        CROSS APPLY ( SELECT    SUM(CASE WHEN record_type IN ( 6, 37, 13 )
                                         THEN quantity
                                         ELSE 0
                                    END) AS SalesVol1
                      FROM      table1 a2
                      WHERE     b.[Date] = a2.transaction_date
                                AND c.sku = a2.product
                                AND series IN ( 62, 236, 501, 52 )
                                AND a2.transaction_week = a.transaction_week
                    ) personalcare
GROUP BY a.transaction_week
ORDER BY a.transaction_week

Pang · Accepted Answer · 2017-08-22 06:02:36Z

0

You should use the UNION operator. Please refer to the query below:

select  a.transaction_week, SalesVol from
(SELECT a.transaction_week as transaction_week,
SUM(CASE WHEN record_type IN (6,37,13) THEN quantity ELSE 0 END) as SalesVol
FROM table 1 a 
LEFT JOIN table 2 b ON b.Date = a.transaction_date
LEFT JOIN table 3 c ON c.sku = a.product
WHERE series in (62,236,501,52)
UNION   
SELECT a.transaction_week as transaction_week,
SUM(CASE WHEN record_type IN (6,37,13) THEN quantity ELSE 0 END) as SalesVol
FROM table 1 a 
LEFT JOIN table 2 b ON b.Date = a.transaction_date
LEFT JOIN table 3 c ON c.sku = a.product
WHERE series in (37,202,203,456)
) AS tbl1
GROUP BY tbl1.transaction_week
ORDER BY tbl1.transaction_week

edited Aug 22, 2017 at 6:02

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Aug 21, 2017 at 12:22

Akshay Raut

866 bronze badges

Collectives™ on Stack Overflow

SQL - SUM within subquery

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related