Can PIVOT in BigQuery generate a "total" column, aggregated across all dimension rows?-CodePudding

Sample table:

 --------- ------- --------- ------ 
| product | sales | quarter | year |
 --------- ------- --------- ------|
| Kale    | 51    | Q1      | 2020 |
| Kale    | 23    | Q2      | 2020 |
| Kale    | 45    | Q3      | 2020 |
| Kale    | 3     | Q4      | 2020 |
| Kale    | 70    | Q1      | 2021 |
| Kale    | 85    | Q2      | 2021 |
| Apple   | 77    | Q1      | 2020 |
| Apple   | 0     | Q2      | 2020 |
| Apple   | 1     | Q1      | 2021 |
 --------- ------- --------- ------

I can pivot it like this:

WITH t AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALL
  SELECT 'Kale', 23, 'Q2', 2020 UNION ALL
  SELECT 'Kale', 45, 'Q3', 2020 UNION ALL
  SELECT 'Kale', 3, 'Q4', 2020 UNION ALL
  SELECT 'Kale', 70, 'Q1', 2021 UNION ALL
  SELECT 'Kale', 85, 'Q2', 2021 UNION ALL
  SELECT 'Apple', 77, 'Q1', 2020 UNION ALL
  SELECT 'Apple', 0, 'Q2', 2020 UNION ALL
  SELECT 'Apple', 1, 'Q1', 2021
)
SELECT *
  FROM t
 PIVOT (
   sum(sales) as total
   FOR quarter in ('Q1', 'Q2')
)
 WHERE year = 2020

and it gives me results:

 --------- ------ ---------- ---------- 
| product | year | total_Q1 | total_Q2 |
 --------- ------ ---------- ---------- 
| Kale    | 2020 |       51 |       23 |
| Apple   | 2020 |       77 |        0 |
 --------- ------ ---------- ----------

Now I want to add column "total" that will contain all sales across product and year. The result would be:

 --------- ------ ---------- ---------- ------- 
| product | year | total_Q1 | total_Q2 | total |
 --------- ------ ---------- ---------- ------- 
| Kale    | 2020 |       51 |       23 |   122 |
| Apple   | 2020 |       77 |        0 |   77  |
 --------- ------ ---------- ---------- -------

How to do this? Is it possible to do using PIVOT or I have to do separate GROUP BY SQL and join it with pivot results?

CodePudding user response：

Take a look at below query: You can calculate total over product and year using an analytic function first and then pivot a table to get the result you want.

WITH t AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALL
  SELECT 'Kale', 23, 'Q2', 2020 UNION ALL
  SELECT 'Kale', 45, 'Q3', 2020 UNION ALL
  SELECT 'Kale', 3, 'Q4', 2020 UNION ALL
  SELECT 'Kale', 70, 'Q1', 2021 UNION ALL
  SELECT 'Kale', 85, 'Q2', 2021 UNION ALL
  SELECT 'Apple', 77, 'Q1', 2020 UNION ALL
  SELECT 'Apple', 0, 'Q2', 2020 UNION ALL
  SELECT 'Apple', 1, 'Q1', 2021
)
SELECT * FROM (
  SELECT *, SUM(sales) OVER (PARTITION BY product, year) AS total FROM t
) PIVOT (SUM(sales) total FOR quarter IN ('Q1', 'Q2'))
WHERE year = 2020
;

output:

CodePudding user response：

Consider below approach

select * from (
  select * from your_table union all
  select product, sum(sales), 'Year', year 
  from your_table
  group by product, year
)
pivot (sum(sales) as total for quarter in ('Year', 'Q1', 'Q2'))
where year = 2020

if applied to sample data in your question - output is