CODEWITHSUNDEEP
sqlgoogle-bigquery
Winston
Winston

Reputation: 326

SQL group by count unique values into separate columns

I have two columns, a and b, and both have categorical values. Say the database looks like this,

a    b
a1   b1
a2   b2
a3   b1
......

I want to group by a and count unique values of b into separate columns, for example,

Value    b1    b2    b3
a1       5     10    3
a2       4     6     7
....

I tried SELECT a, b, count(b) FROM table GROUP BY a, b and got something similar like this:

a1    b1    5
a1    b2    10
....

What's the SQL query to produce the desired output? Thanks.

Upvotes: 1

Views: 509

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173013

Below is for BigQuery Standard SQL

SELECT
  a,
  COUNTIF(b = 'b1') AS b1,
  COUNTIF(b = 'b2') AS b2,
  COUNTIF(b = 'b3') AS b3
FROM t
GROUP BY a
-- ORDER BY a

Upvotes: 4

The AG
The AG

Reputation: 690

A simple approach to achieve would be:

Select a, 
count(CASE WHEN b = 'b1' THEN 1 ELSE NULL END) b1,
count(CASE WHEN b = 'b2' THEN 1 ELSE NULL END) b2,
count(CASE WHEN b = 'b2' THEN 1 ELSE NULL END) b3
from table
group by a
order by 1

Upvotes: 1

The Impaler
The Impaler

Reputation: 48810

You can perform conditional addition. For example:

select
  a,
  sum(case when b = 'b1' then 1 else 0 end) as b1,
  sum(case when b = 'b2' then 1 else 0 end) as b2,
  sum(case when b = 'b3' then 1 else 0 end) as b3
from t
group by a
order by a

Upvotes: 3

Related Questions