Grouping my subquery the same way I group my base query SQL

Question

I am having difficulty with my subquery returning proper values. As you can see from the query and results below I am trying to use a subquery to return a column that uses a different date range for the margin_2020 column

SELECT os.ga_mapping as channel
, SUM(os.margin) as margin_2019
, (select sum(os.margin)
   from orders_summary os
   where os.date_order between '2020-10-01' and '2020-12-31'
   group by 2) as margin_2020
, ((margin_2020 - margin_2019) / margin_2019) * 100 as rate_of_change

FROM orders_summary os
WHERE os.date_order BETWEEN '2019-07-01' AND '2019-09-31'
GROUP BY 1;

Looking at the 3rd column 'margin_2020' each row returns the same value rather than filtering by channel. Such as it is doing in margin_2019 column. How do I apply the same grouping logic to the subquery so that margin_2020 is broken out by channel?

Thanks for any help or advice.

Answer 1

You just need to corelate it using ga_mapping as follows:

SELECT os.ga_mapping as channel
, SUM(os.margin) as margin_2019
, (select sum(oss.margin)
   from orders_summary oss
   where oss.date_order between '2020-10-01' and '2020-12-31'
     and oss.ga_mapping = os.ga_mapping) as margin_2020 -- extra condition
, ((margin_2020 - margin_2019) / margin_2019) * 100 as rate_of_change
FROM orders_summary os
WHERE os.date_order BETWEEN '2019-07-01' AND '2019-09-31'
GROUP BY 1;

You can also avoid subquery by using the conditional aggregation as follows:

SELECT os.ga_mapping as channel
, SUM(case when os.date_order BETWEEN '2019-07-01' AND '2019-09-31' then os.margin end) as margin_2019
, SUM(case when os.date_order BETWEEN '2020-10-01' and '2020-12-31' then os.margin end) as margin_2020
, ((margin_2020 - margin_2019) / margin_2019) * 100 as rate_of_change
FROM orders_summary os
WHERE os.date_order BETWEEN '2019-07-01' AND '2019-09-31'
   or os.date_order between '2020-10-01' and '2020-12-31'
GROUP BY 1;