CODEWITHSUNDEEP
google-bigquery
Peter Nagel
Peter Nagel

Reputation: 181

BigQuery Question: Calculate SUM from different rows, then add as a new row

I have a question about SQL/BigQuery. I made a simple dataset in the screenshot that has 3 products and a total row (which is classified as a product as well). The "OTHER" product is calculated by substracting product A, B, C from TOTAL.

How can I write a query that would calculate "OTHER" for each day? In the screenshot in red is the result I would want, the rows in white is the data I have in BigQuery. I assume I have to use SUM() OVER with a UNION? Yet I havent figured it outenter image description here

Upvotes: 1

Views: 3270

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Consider below

select * from your_table union all
select date, 'OTHER', sum(quantity * if(product = 'TOTAL', 1, -1)) 
from your_table
group by date

Upvotes: 1

Damião Martins
Damião Martins

Reputation: 1849

An option to solve using only SUM and UNION ALL:

SELECT * FROM table
UNION ALL
SELECT 
  date,
  'OTHER' as product,
  SUM(IF(product = 'TOTAL', quantity, 0))-SUM(IF(product != 'TOTAL', quantity, 0)) as quantity
FROM table
GROUP BY date

Upvotes: 2

Related Questions