So i have this code:
SELECT a.total_sales AS July, b.total_sales AS August, c.total_sales AS September
FROM
(SELECT EXTRACT(month FROM delivered_at) AS month, ROUND(SUM (sale_price),2) AS total_sales
FROM `bigquery-public-data.thelook_ecommerce.order_items`
WHERE status = 'Complete' AND delivered_at BETWEEN "2022-01-01" AND "2022-10-01"
GROUP BY month
ORDER BY month) a,
(SELECT EXTRACT(month FROM delivered_at) AS month, ROUND(SUM (sale_price),2) AS total_sales
FROM `bigquery-public-data.thelook_ecommerce.order_items`
WHERE status = 'Complete' AND delivered_at BETWEEN "2022-01-01" AND "2022-10-01"
GROUP BY month
ORDER BY month) b,
(SELECT EXTRACT(month FROM delivered_at) AS month, ROUND(SUM (sale_price),2) AS total_sales
FROM `bigquery-public-data.thelook_ecommerce.order_items`
WHERE status = 'Complete' AND delivered_at BETWEEN "2022-01-01" AND "2022-10-01"
GROUP BY month
ORDER BY month) c
WHERE a.month = 7 AND b.month = 8 AND c.month = 9
I got the result that i wanted, which is this:
Row July August September
1 148622.29 169310.62 209339.57
Is there any simpler ways to do this?
CodePudding user response:
We can reduce 3 subquerys into 1 subquery
SELECT
SUM(IF(t.month=7,t.total_sales,0)) AS July,
SUM(IF(t.month=8,t.total_sales,0)) AS August,
SUM(IF(t.month=9,t.total_sales,0)) AS September
FROM
(
SELECT EXTRACT(month FROM delivered_at) AS month, ROUND(SUM (sale_price),2) AS total_sales
FROM `bigquery-public-data.thelook_ecommerce.order_items`
WHERE status = 'Complete' AND delivered_at BETWEEN "2022-01-01" AND "2022-10-01"
AND month in(7,8,9)
GROUP BY month
) t