How to count occurrences of a column value per ID in BigQuery

Question

If I have a table like this in BigQuery

id success ------------- 01 true 01 true 02 false 02 true

I would like to end up with this:

id true false -------------------- 01 2 0 02 1 1

I've got this but I wondered if there was a more elegant way to do it?

SELECT t1.id, ( SELECT count(*) FROM `my_table` t2 WHERE t2.success = true and t2.id = t1.id ) as trueCount, ( SELECT count(*) FROM `my_table` t3 WHERE t3.success = false and t3.id = t1.id ) as falseCount FROM `my_table` t1 GROUP BY id

I think that's best what you can get. But... ID usually is primary key and must be unique. I hope it's just for demo — Justinas
– Justinas, Commented Mar 24, 2022 at 14:50

Parfait · Accepted Answer · 2022-03-24 15:01:09Z

5

Consider conditional aggregation which should work on almost all databases:

SELECT t.id, SUM(CASE WHEN t.success = true THEN 1 ELSE 0 END) AS trueCount, SUM(CASE WHEN t.success = false THEN 1 ELSE 0 END) AS falseCount FROM `my_table` t GROUP BY t.id

edited Mar 24, 2022 at 15:01

answered Mar 24, 2022 at 14:57

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nbk Over a year ago

where come t2 and t3 from, this query makes no sense, besides without nknowig the rdms...

Parfait Over a year ago

@nbk...whoops! Fixed. Again, this should work on most RDBMS's that support CASE.

nbk Over a year ago

that is better, but still the question lacks vital detail and should be closed

tarling Over a year ago

@nbk Have updated the title and question text to mention that I'm using BigQuery. t2 and t3 are table aliases.

D-Shih · Accepted Answer · 2022-03-24 14:57:21Z

You can try to use condition aggregate function instead of the subquery.

SELECT id, COUNT(CASE WHEN success = true THEN 1 END) trueCount, COUNT(CASE WHEN success = false THEN 1 END) falseCount FROM T GROUP BY id

Mikhail Berlyant · Accepted Answer · 2022-03-24 22:13:17Z

consider below approach for BigQuery

select * from your_table pivot (count(*) for success in (true, false))

if applied to sample data in your question - output is

Collectives™ on Stack Overflow

How to count occurrences of a column value per ID in BigQuery

3 Answers 3

4 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related