Thread: SQL - histogram

Hi,
I've a problem as follow:
I have a table where one record represent a person, including his height.
I'd like to know how many person have more than 150 cm, more than 160 cm 
and so on.
How can I do that using SQL?

Example:
people > 150 - 1000      > 160 -  850      > 170 -  500      > 180 -  200      > 190 -  30
thanks in advance

From: "Txugo" <msalt@uol.com.br>

> I have a table where one record represent a person, including his height.
> I'd like to know how many person have more than 150 cm, more than 160 cm
> and so on.
> How can I do that using SQL?
>
> Example:
> people > 150 - 1000
>        > 160 -  850
>        > 170 -  500
>        > 180 -  200
>        > 190 -  30
> thanks in advance

richardh=> select * from people;id | height
----+-------- 1 |    150 2 |    155 3 |    160 4 |    165
(4 rows)

richardh=> select * from heights;cm
-----150160
(2 rows)

richardh=> select cm,count(id) from people, heights where height>=cm group
by cm;cm  | count
-----+-------150 |     4160 |     2
(2 rows)

HTH

- Richard Huxton

One way to do this is to use the 'CASE' expression, documented here:

http://pgsql.dbexperts.com.br/devel-corner/docs/postgres/functions-conditional.html

Because you have only one record per person, you can use the 'COUNT'
aggregate function to see how many of each height category match. An
example SQL statement for your height analysis would be:

SELECT CASE
WHEN height < 150 THEN '< 150'
WHEN height BETWEEN 150 AND 160 THEN '150 - 160'
WHEN height BETWEEN 160 AND 170 THEN '160 - 170'
WHEN height BETWEEN 170 AND 180 THEN '170 - 180'
WHEN height BETWEEN 180 AND 190 THEN '180 - 190'
WHEN height BETWEEN 190 AND 200 THEN '190 - 200'
ELSE '> 200' END AS category,
COUNT(*) AS qty
FROM person_table GROUP BY category;

On 9 Jul 2001, Txugo wrote:

> Hi,
> I've a problem as follow:
> I have a table where one record represent a person, including his height.
> I'd like to know how many person have more than 150 cm, more than 160 cm
> and so on.
> How can I do that using SQL?
>
> Example:
> people > 150 - 1000
>        > 160 -  850
>        > 170 -  500
>        > 180 -  200
>        > 190 -  30
> thanks in advance