CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Surjya Narayana Padhi
Surjya Narayana Padhi

Reputation: 7841

how to output result of group by of two columns with one column values as row and another as columns?

I have table like this

id  col1    col2
1   A       1
2   B       0
3   A       1
4   C       1
5   B       0
6   A       0
7   C       1
8   C       1
9   B       1
10  B       0

I need a query something like this

Values  1   0
A       2   1
B       1   3
C       3   0

In the above result the header shows the col2 distinct values (1,0) and rows names represents distinct values of col1. The values in the table shows the counts.

Any suggestion to get the result like this in postgresql?

Upvotes: 1

Views: 62

Answers (3)

Michel Milezzi
Michel Milezzi

Reputation: 11155

You could also use FILTER:

SELECT 
    col1,
    COUNT(*) FILTER (WHERE col2 = 1) AS 1,
    COUNT(*) FILTER (WHERE col2 = 0) AS 0,      
FROM 
    foo
GROUP BY 
    col1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Here are simpler ways to write this logic. The first is Postgres-specific:

select col1,
       sum( (col2 = 1)::int ) as num_1,
       sum( (col2 = 0)::int as num_0
from t
group by col1;

The second just uses arithmetic:

select col1,
       sum( col2 ) as num_1,
       sum( 1 - col2 ) as num_0
from t
group by col1;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need conditional aggregation :

select col1,
       sum(case when col2 = 1 then 1 else 0 end) as 1,
       sum(case when col2 = 0 then 1 else 0 end) as 0
from table t
group by col1;

Upvotes: 2

Related Questions