CODEWITHSUNDEEP
sqlgoogle-bigquery
Helen
Helen

Reputation: 11

How to select distinct values (more than one value) in bigquery?

I have to make a query where I get the number of unique values from a database. In this database(type of jobs). In the column "jobs" there are strings like "doctor, lawyer" or "paramedic, banker, teacher". When I do the query:

SELECT DISTINCT jobs FROM Type_Of_Jobs

I get some rows that only include one string, but I need combinations (so not just one value) and I need to be able to return # of those unique combinations. How do I do this in bigquery? Thank you!

Upvotes: 0

Views: 1737

Answers (2)

Suds
Suds

Reputation: 156

Are you looking for a simple count for each combination of job string as it is or you want each combination to be split into the individual job and distinct count of the individual job?

For each combination of job string as it is:

SELECT jobs, count(*) count
FROM Type_Of_Jobs
group by jobs

For a distinct count of the individual job, look at Gordon's answer.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Use split() and unnest():

select job, count(*)
from type_of_jobs tj cross join
     unnest(split(jobs, ',')) job
group by job;

Upvotes: 1

Related Questions