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

4

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

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Mar 14, 2013 at 23:37

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

3

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

answered Mar 12, 2013 at 1:48

Taryn

249k57 gold badges375 silver badges409 bronze badges

Collectives™ on Stack Overflow

Crosstab query in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related