Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Question Unanswered: counting elements in interval (ie 0-10, 10-20, 20-30)

    Hi all,

    suppose i have a list o people with their age, i want to group and count them as follows

    numb. of people sum(age) avg(age)
    0-10
    10-20
    20-30

    how can i do that?

    I can make one query and then run it three times with the parameters of each interval.. but what if i have more intervals?

    I would like to do it in a unique elegant query

    Thanks you very much

    Matteo

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Question Re: counting elements in interval (ie 0-10, 10-20, 20-30)

    Do you mean group by age group, like that:
    0 - 10 years old, average age for that group,
    10-20 years old, average age for that group,
    30-40 years old, average age for that group and so on?

    If so, this could work for you:

    SELECT GROUP0, SUM(SUM_AGE), AVG(AVG_AGE)
    FROM (
    SELECT TRUNC(AGE, -1) AS GROUP0,
    (COUNT0 * AGE) AS SUM_AGE,
    ((COUNT0 * AGE) / COUNT0) AVG_AGE
    FROM (
    SELECT COUNT(*) AS COUNT0 , AGE
    FROM TABLE.PEOPLE
    GROUP BY AGE)
    AS TEMP1)
    AS TEMP2
    GROUP BY GROUP0

    (runs OK with DB2 for z/OS)
    Cheers, Bill

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Re: counting elements in interval (ie 0-10, 10-20, 20-30)

    I'd probably do it like this...
    Code:
    select (age-1)/10*10+10  upper_age_boundary
    ,      count(*)          frequency
    ,      avg(decimal(age)) mean
    from   yourTable
    group by (age-1)/10*10+10
    Damian

  4. #4
    Join Date
    Mar 2004
    Location
    Belarus
    Posts
    5
    Hi guys!

    Why have you forgot about CASE ?
    What if the interval cannot be calculated so easy?

    Here is the 'elegant' solution, as it seems for me.
    :-)

    with t(age) as (values (1),(5),(5),(4),(6),(10),(12),(15),(17),(20),(21), (22),(22),(24),(30) )
    , x (age_group ,age) as (
    select
    case when t.age between 1 and 10 then 'up to ten years'
    when t.age between 11 and 20 then 'from ten to twenty'
    when t.age between 21 and 30 then 'from twenty to thirty'
    else 'out of range'
    end
    ,t.age
    from t
    )
    select x.age_group ,count(*) ,avg(age)
    from x
    group by x.age_group

    Regards,
    Andrew

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Hi guys!

    Why have you forgot about CASE ?
    What if the interval cannot be calculated so easy?

    Here is the 'elegant' solution, as it seems for me.
    :-)
    I don't think anyone's forgotten about using CASE statements. The problem with that approach, is that it is not dynamic as you have to code for each interval. I take your point though.

    I also can't think of any 'regular' interval that could not easily be calculated.

    Horses for courses, as they say :-)

    Damian

  6. #6
    Join Date
    Mar 2004
    Posts
    2

    Thumbs up

    Thank you all guys,

    i used the CASE solution, since it fitted better my problem, anyway the other way suggested by andrew was elegant as well.

    Thanks for your help.

    Matteo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •