Re: Cross-classified table

Поиск
Список
Период
Сортировка
Искать

Re: Cross-classified table

От:
"Muhyiddin A.M Hayat" <middink@indo.net.id>
Дата:
> 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

Cross-classified table

От:
Alexander Pucher <pucher@atlas.gis.univie.ac.at>
Дата:
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

От:
Bruno LEVEQUE <bruno.leveque@net6d.com>
Дата:
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
FAQ