Search code examples
sqlcountgoogle-bigquerystring-aggregation

Count each values in each row after string_agg in SQL

I have a SQL table with one column in the end. I group by id and string_agg titles.

 ID     Title
  1        a
  1        a
  1        b
  2        c
  2        a

I have this table below right now.

  Titles
  a,a,b
  c,a

I want to count each value in each row in string agg as a new column. I tried count distinct or Count("a") which returned count of all values in one row. My expected outcome would be

Titles count_a count_b count_c count_all
a,a,b    2       1       0        3
c,a      1       0       1        2

Is this possible to calculate in SQL?

Solution

  • You can use conditional aggregation:

    select id, string_agg(title, ',' order by title) as titles,
       countif(title = 'a') as num_as,
       countif(title = 'b') as num_bs,
       countif(title = 'c') as num_cs,
       count(*) as total
from t
group by id;