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

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

CodePudding user response：

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

CodePudding user response：

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