CODEWITHSUNDEEP
google-bigquery
Igor Gervasiychuk
Igor Gervasiychuk

Reputation: 33

Is it possible to calculate grand totals in BigQuery for repeated fields w/ rollup?

There is a similar question ( Is BigQuery ROLLUP supports grouping by repeated fields ), but it lacks an example.

Consider following code:

SELECT    user_segments AS user_segments,
SUM(impressions) AS imps,
SUM(clicks) AS clicks,
FROM [theTable] 
GROUP BY ROLLUP (user_segments)
ORDER BY imps DESC
LIMIT 1000

Where theTable contains impressions and clicks of two users (table only has 10 rows, and impressions = 1 on every row):

{"impressions": 1, "user_segments": [0, 1], "user_id": "A0", "clicks": 0}
{"impressions": 1, "user_segments": [1, 2], "user_id": "A1", "clicks": 1}
{"impressions": 1, "user_segments": [0, 1], "user_id": "A0", "clicks": 2}
{"impressions": 1, "user_segments": [1, 2], "user_id": "A1", "clicks": 0}
{"impressions": 1, "user_segments": [0, 1], "user_id": "A0", "clicks": 1}
{"impressions": 1, "user_segments": [1, 2], "user_id": "A1", "clicks": 2}
{"impressions": 1, "user_segments": [0, 1], "user_id": "A0", "clicks": 0}
{"impressions": 1, "user_segments": [1, 2], "user_id": "A1", "clicks": 1}
{"impressions": 1, "user_segments": [0, 1], "user_id": "A0", "clicks": 2}
{"impressions": 1, "user_segments": [1, 2], "user_id": "A1", "clicks": 0}

Query output is:

user_segments imps clicks
null          20   18
1             10   9
2             5    4
0             5    5

But there are only 10 (ten!) impressions in the table. In my opinion correct values for totals would be:

user_segments imps clicks
null          10   9
1             10   9
2             5    4
0             5    5

Is there any way to get to the correct totals w/o a separate query? Thanks!

Upvotes: 3

Views: 409

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

below is obvious workaround you most likely using already - but still posting just in case 

SELECT * FROM (
  SELECT 
    user_segments AS user_segments,
    SUM(impressions) AS imps,
    SUM(clicks) AS clicks
  FROM theTable
  GROUP BY user_segments
), (
  SELECT 
    NULL AS user_segments,
    SUM(impressions) AS imps,
    SUM(clicks) AS clicks
  FROM theTable
)
ORDER BY imps DESC, user_segments

Upvotes: 1

Related Questions