SQL: SELECT AS multiple value with the same FROM with different WHERE-CodePudding

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