Crosstab query in PostgreSQL

Question

I would like the result of this query in a crosstab:

SELECT district, sex ,count(sex) FROM table1 GROUP BY sex, district ORDER BY district; district | sex | count ---------+-----+----- dis_1 | M | 2 dis_1 | F | 4 dis_1 | NI | 1 dis_2 | M | 5 dis_2 | F | 2

Like this:

district | M | F | NI ---------+---+---+--- dis_1 | 2 | 4 | 1 dis_2 | 5 | 2 | 0

I did some testing without success, as the query below:

SELECT row_name AS district, category_1::varchar(10) AS m, category_2::varchar(10) AS f, category_3::varchar(10) AS ni, category_4::int AS count FROM crosstab('select district, sex, count(*) from table1 group by district, sex') AS ct (row_name varchar(27), category_1 varchar(10), category_2 varchar(10), category_3 varchar(10), category_4 int);

Community · Accepted Answer · 2017-05-23 12:00:42Z

This crosstab function produces exactly what you asked for (except for simplified data types):

SELECT * FROM crosstab(' SELECT district, sex, count(*)::int FROM table1 GROUP BY 1,2 ORDER BY 1,2' ,$$VALUES ('M'), ('F'), ('NI')$$) AS ct (district text ,"M" int ,"F" int ,"NI" int);

You had a couple of errors in your attempt.
Find details and explanation in this closely related answer:
PostgreSQL Crosstab Query

Taryn · Accepted Answer · 2013-03-12 01:48:38Z

You can use an aggregate function with a CASE expression to get the result in columns:

select district, sum(case when sex ='M' then 1 else 0 end) M, sum(case when sex ='F' then 1 else 0 end) F, sum(case when sex ='NI' then 1 else 0 end) NI from table1 group by district order by district

Collectives™ on Stack Overflow

Crosstab query in PostgreSQL

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related