CODEWITHSUNDEEP
sqlgoogle-bigquery
hamsy
hamsy

Reputation: 369

Aggregation over STRUCT in BQ

When I ran the following query 

WITH total_count AS (
  SELECT
    id,
    pid,
    click_ts,
    stacked ,
    SUM(stacked) OVER (PARTITION BY id, pid) AS total_count
  FROM limited_count),   
stacked AS (
  SELECT *
  FROM total_count
  WHERE stacked > 1
)

SELECT
  id,
  pid,
  total_count,
  SUM(stacked) AS stacked 
FROM stacked
GROUP BY 1, 2, 3

I receive an error:

No matching signature for aggregate function SUM for argument types: STRUCT. Supported signatures: SUM(INT64); SUM(FLOAT64) at [21:47]

When I try to do SUM(stacked) on the following view:

WITH total_count AS (
  SELECT
    id,
    pid,
    click_ts,
    stacked ,
    SUM(stacked) OVER (PARTITION BY id, pid) AS total_count
  FROM limited_count), 

SELECT *
FROM total_count
WHERE stacked > 1

Everything works fine! Any ideas how to make it in the same query?

Upvotes: 3

Views: 7686

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

#standardSQL
WITH total_count AS (
  SELECT id, pid, click_ts, stacked ,
  SUM(stacked) OVER (PARTITION BY id, pid) AS total_count
  FROM limited_count), 
stacked AS (
  SELECT *
  FROM total_count
  WHERE stacked > 1 ) 
SELECT id, pid, total_count, SUM(stacked.stacked) AS stacked 
FROM stacked
GROUP BY 1,2, 3

Upvotes: 2

Related Questions