Re: Cross-classified table - Mailing list pgsql-novice

> Hi,
> I'm looking for the correct syntax of getting a cross-classified table
> from my SELECT statement.
>
> Let's pretend I have this table:
>
>
> origin        sex
> ----------------
> USA        male
> USA        female
> China      male
> China      male
> UK           male
> USA        male
>
>
> and I want as result something like:
>
>                male      female
>
> USA        2            1
> China      2            0
> UK           1            0
>
>
> How can I get this?
>

You can use below SQL

SELECT
  origin,
  SUM(
   CASE
     WHEN (sex ='male') THEN 1
     ELSE 0
   END
  ) AS male,
  SUM(
   CASE WHEN (sex ='female') THEN 1
     ELSE 0
   END
  ) AS female

FROM
  table1
GROUP BY origin

 Best regards,
Muhyiddin A.M Hayat