Generating a basic sales report

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

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.