Re: Decode and Ranges?

Hi,

Smart solution. I would have used the sign function to make up the groups for the counts, but your method is surely more simple. I just have one correction: As I can see you get a miscount by using round - use trunc instead. I am not 37 years old, but your computation says I am 36.824291 years, and when you round that....:-)

regards,

Michael Ringbo

Sybrand Bakker wrote:

> Hi,
>
> This should look like
> select gender_cd,
> , sum(decode(round(months_between(birth_dt,sysdate)/12) between 6 and
> 10,1,0)
> , sum(decode(round(months_between(birth_dt,sysdate)/12) between 11 and 15,
> 1, 0)
> from members
> where ...
> group by gender_cd
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> JCEdleman wrote:
>
> > Hi,
> >
> > Here is what I am trying to do:
> >
> > In one SQL statement using Decode ( or another function you might suggest),
> > validate a numeric range so . . .
> >
> > If the row value is between 6 and 10 display a count of records.
> >
> > Psydo -code:
> > Select gender_cd,
> > count(decode(round(months_between(birth_dt,sysdate)/12),between 6 and 10, total
> > count))) "Age 6-10"
> > from members
> > where . . . . .
> >
> > Result would look like this:
> >
> > Age 6-10 Age 11-15
> > Males 5 10
> > Females 6 22
>
> ------------------------------------------------------------------------
>
> Sybrand Bakker <postbus_at_sybrandb.demon.nl>
>
> Sybrand Bakker
> <postbus_at_sybrandb.demon.nl>
> Netscape Conference Address
> Netscape Conference DLS Server
> Additional Information:
> Last Name Bakker
> First Name Sybrand
> Version 2.1
Received on Mon Mar 01 1999 - 08:20:50 CST