Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cross-classified table

Cross-classified table

From
Alexander Pucher
Date:
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?

Best regards,
alex

Re: Cross-classified table

From
Bruno LEVEQUE
Date:
The query :

select origin, sex, count(sex) from table group by origin, sex;

gives the result :

 origin |  sex   | count
--------+--------+-------
 China  | male   |     2
 UK     | male   |     1
 USA    | female |     1
 USA    | male   |     2
(4 rows)


Is it enought ?

Bruno



Alexander Pucher wrote:

> 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?
>
> Best regards,
> alex
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com

Re: Cross-classified table

From
"Muhyiddin A.M Hayat"
Date:
> 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