pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL - histogram
3+ messages / 3 participants
[nested] [flat]
* SQL - histogram
@ 2001-07-09 17:19 Txugo <msalt@uol.com.br>
0 siblings, 2 replies; 3+ messages in thread
From: msalt@uol.com.br @ 2001-07-09 17:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SQL - histogram
@ 2001-07-11 04:39 Mathew White <innards@xmission.com>
parent: Txugo <msalt@uol.com.br>
1 sibling, 0 replies; 3+ messages in thread
From: Mathew White @ 2001-07-11 04:39 UTC (permalink / raw)
To: Txugo <msalt@uol.com.br>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SQL - histogram
@ 2001-07-11 09:06 Richard Huxton <dev@archonet.com>
parent: Txugo <msalt@uol.com.br>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2001-07-11 09:06 UTC (permalink / raw)
To: Txugo <msalt@uol.com.br>; pgsql-sql
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
-----
150
160
(2 rows)
richardh=> select cm,count(id) from people, heights where height>=cm group
by cm;
cm | count
-----+-------
150 | 4
160 | 2
(2 rows)
HTH
- Richard Huxton
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-07-11 09:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-09 17:19 SQL - histogram Txugo <msalt@uol.com.br>
2001-07-11 04:39 ` Mathew White <innards@xmission.com>
2001-07-11 09:06 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox