pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mathew White <innards@xmission.com>
To: Txugo <msalt@uol.com.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL - histogram
Date: Tue, 10 Jul 2001 22:39:01 -0600 (MDT)
Message-ID: <Pine.LNX.4.33.0107102227380.9022-100000@innards> (raw)
In-Reply-To: <45809a56.0107090919.177def5b@posting.google.com>

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




view thread (3+ messages)  latest in thread

Message-ID: <Pine.LNX.4.33.0107102227380.9022-100000@innards>
Permalink:  ../Pine.LNX.4.33.0107102227380.9022-100000@innards/
Also on:    postgresql.org/message-id/Pine.LNX.4.33.0107102227380.9022-100000@innards

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: innards@xmission.com, msalt@uol.com.br
  Subject: Re: SQL - histogram
  In-Reply-To: <Pine.LNX.4.33.0107102227380.9022-100000@innards>
  References: <45809a56.0107090919.177def5b@posting.google.com> <Pine.LNX.4.33.0107102227380.9022-100000@innards>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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