CODEWITHSUNDEEP
google-bigquery
Rabih Maatouk
Rabih Maatouk

Reputation: 21

Row Aggregation value in big query

How i can get the column C using big-query (Stucture of bigquery table is "id","revenue")

column C = Aggregate value of the revenue for each row

Table

id, revenue, C

1, 5, 5
2, 3, 8
3, 1, 9
4, 2, 11
5, 0, 11

Upvotes: 0

Views: 1977

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

below is for BigQuery Standard SQL 

#standardSQL
SELECT id, SUM(revenue) OVER(ORDER BY id) AS total_revenue
FROM `project.dataset.yourTable`
ORDER BY id

You can use below dummy data to test / play with it 

#standardSQL
WITH `project.dataset.yourTable` AS (
  SELECT 1 id, 5 revenue UNION ALL
  SELECT 2, 3 UNION ALL
  SELECT 3, 1 UNION ALL
  SELECT 4, 2 UNION ALL
  SELECT 5, 0 
)
SELECT id, SUM(revenue) OVER(ORDER BY id) AS total_revenue
FROM `project.dataset.yourTable`
ORDER BY id

output is 

id  total_revenue    
1   5    
2   8    
3   9    
4   11   
5   11

Upvotes: 0

Elliott Brossard
Elliott Brossard

Reputation: 33745

SELECT id, SUM(revenue) AS total_revenue
FROM YourTable
GROUP BY id
ORDER BY id;

If you also want the revenue total across all id values, you can use GROUP BY ROLLUP. The resulting row with a NULL id contains the total across all ids:

SELECT id, SUM(revenue) AS total_revenue
FROM YourTable
GROUP BY ROLLUP(id)
ORDER BY id;

Edit: for a cumulative sum, you can use an analytic function. In this case, it would be:

SELECT id, SUM(revenue) OVER (ORDER BY id) AS total_revenue
FROM YourTable
ORDER BY id;

Upvotes: 1

Related Questions