SQL grouping/counting on a string split function

Question

Ok so my original is this

select people, count(*) 
from table
group by people

but some of the people have multiple people so this aggregation will not give you pure counts for A, B, C but also each iteration

A 10
B 5
A, B 1
A, C 2
C 15
A, B, C 3

etc.

This works to get the full list of individuals in legacy sql

select split(people,",") as person
from table

But I cannot use the group by on it

select split(people,",") as person, count(*)
from table
group by person

gives the error

Cannot group by an aggregate.

I feel like the solution is a subquery, somehow, but I'm not sure how to execute it

I'm working in bigquery and I get "you cannot use standard sql against a legacy sql view" errors if I switch — J. G., Aug 15 '19 at 16:54

score 1 · Accepted Answer · answered Aug 15 '19 at 16:53

1

Try wrap with an outer query

select person, count(*)
from(
    select split(people,",") as person
    from table
) t
group by person

answered Aug 15 '19 at 16:53

Serg

1 Answers1