CODEWITHSUNDEEP
google-bigquery
Shivam Chauhan
Shivam Chauhan

Reputation: 113

Bigquery - How to Calculate the sum of two continuous rows

How can I get the sum of two rows clubbed together for instance If I have 5 rows in total, I should get 3 rows a result.

Below is my table:

2020-08-01 1
2020-08-02 3
2020-08-03 4
2020-08-04 2
2020-08-05 4

I want to achive this:

4
6
4

August 1 and 2 = 4
August 3 and 4 = 6
August 5 = 4

Upvotes: 0

Views: 354

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below is for Bigquery Standard SQL

#standardSQL
SELECT SUM(value) AS value, 
  STRING_AGG(FORMAT_DATE('%B %d', day), ' and ') || ' = ' || CAST(SUM(value) AS STRING) AS calc
FROM (
  SELECT day, value, DIV(ROW_NUMBER() OVER(ORDER BY day) - 1, 2) grp
  FROM `project.dataset.table` t
)
GROUP BY grp
ORDER BY grp

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATE '2020-08-01' day, 1 value UNION ALL
  SELECT '2020-08-02', 3 UNION ALL
  SELECT '2020-08-03', 4 UNION ALL
  SELECT '2020-08-04', 2 UNION ALL
  SELECT '2020-08-05', 4 
)
SELECT SUM(value) AS value, 
  STRING_AGG(FORMAT_DATE('%B %d', day), ' and ') || ' = ' || CAST(SUM(value) AS STRING) AS calc
FROM (
  SELECT day, value, DIV(ROW_NUMBER() OVER(ORDER BY day) - 1, 2) grp
  FROM `project.dataset.table` t
)
GROUP BY grp
ORDER BY grp

with output

Row value   calc     
1   4       August 01 and August 02 = 4  
2   6       August 03 and August 04 = 6  
3   4       August 05 = 4

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522732

You could use ROW_NUMBER here:

WITH cte AS (
    SELECT dt, val, ROW_NUMBER() OVER (ORDER BY dt) rn
    FROM yourTable
)

SELECT SUM(val)
FROM cte
GROUP BY FLOOR((rn - 1) / 2)
GROUP BY MIN(dt);

Here is a demo link, shown in SQL Server, but whose logic should also be working for BigQuery:

Demo

Upvotes: 1

Related Questions