Generating a basic sales report

Question

So close to finish generating a report using SQL only. Very proud, but I'm stuck because I know I'm doing something the long way (the subquery in the SELECT statement).

I'm trying to fix the jan2019_sales, because its just giving me the SUM of sales in total, but I need it divided into the divisional sales for 2019, but I know that a subquery can only return 1 column, so I can't have both the division and the sales come up for 2019.

What's the best way to tackle this simple piece??? Ugh. I made a region for the portion that I (strongly believe) is the issue, wherein there is a much easier way. Here's a picture of what I am trying to fix (column C):

SELECT
    t2.new_division AS division,
    SUM(extended_amount) AS jan2020_sales,
-- region
    ((SELECT
    t2.new_division AS division,
    SUM(extended_amount)
FROM 
    mdwh.us_raw.l_dmw_order_report t1 INNER JOIN item_master_zs t2 ON SUBSTRING(t1.upc,1,6) = t2.item_code
WHERE 
    quantity_ordered > 0
    AND UPPER(line_status) NOT IN ('','RETURN', 'CANCELLED')
    AND UPPER(item_description_1) NOT IN ('','FREIGHT', 'RETURN LABEL FEE', 'VISIBLE STITCH')
    AND (quantity_ordered * unit_price_amount) > 0
    AND oms_order_date BETWEEN '2019-01-01' AND '2019-01-31'
GROUP BY
    division
ORDER BY
    division)) AS jan2019_sales,
-- endregion
    ROUND((jan2020_sales / jan2019_sales * 100)) || '%' AS pct_change
FROM 
    mdwh.us_raw.l_dmw_order_report t1 INNER JOIN item_master_zs t2 ON SUBSTRING(t1.upc,1,6) = t2.item_code
WHERE 
    quantity_ordered > 0
    AND UPPER(line_status) NOT IN ('','RETURN', 'CANCELLED')
    AND UPPER(item_description_1) NOT IN ('','FREIGHT', 'RETURN LABEL FEE', 'VISIBLE STITCH')
    AND (quantity_ordered * unit_price_amount) > 0
    AND oms_order_date BETWEEN '2020-01-01' AND '2020-01-31'
    AND t2.new_division BETWEEN '11' AND '38'
GROUP BY 
    division
ORDER BY 
    division

In your 2019 sales query, you have to have that division = your outer query division. You may want to alias the queries differently so you can reference the outer query. — SS_DBA, Feb 13 '20 at 23:37
I've never used Amazon-Redshift.. but if ever i was posting a question like this, I would give a link with some data, and show what I was trying to get as an outcome. That way people can tinker and try and solve your problem, and they don't have to set something up themselves. Not sure if Googling Redshift Playground will give you a website to link something similar to db-fiddle, sqlfiddle etc. — JGFMK, Feb 13 '20 at 23:41
@SS_DBA I get the concept, but having trouble translating that into SQL. How can I relate the divisions within a subquery, when the divisions can't even be used as a column for the 2019 query? Hm — Z41N, Feb 13 '20 at 23:53
Change your inner query alias' to `t3` and `t4`. Then in your inner query `Where` clause. Add, `t2.new_division = t4.new_division`. Give that a shot and see. — SS_DBA, Feb 13 '20 at 23:56

score 1 · Accepted Answer · answered Feb 18 '20 at 12:59

One of the issues here is that you have the same alias' for both inner (2019) and outer (2020) queries. Also, the inner query is returning multiple rows because there is no join on the division for the different year sales.

In your 2019 sales query (inner query), you have to have that division joined to your outer query (2020) division. You may want to alias the queries differently so you can reference the outer query.

Change your inner query alias' to t3 and t4. Then in your inner query Where clause. Add, t2.new_division = t4.new_division.

Generating a basic sales report

1 Answers1