CODEWITHSUNDEEP
sqlpostgresqlcasepostgresql-9.1crosstab
Marcello Benigno
Marcello Benigno

Reputation: 113

Crosstab query in PostgreSQL

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);

Upvotes: 1

Views: 3395

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656521

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

Upvotes: 4

Taryn
Taryn

Reputation: 247690

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

Upvotes: 3

Related Questions