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

1. Registered User
Join Date
Mar 2004
Posts
2

## 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. Registered User
Join Date
Jan 2004
Location
Europe, Finland, Helsinki
Posts
64

## 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

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. Registered User
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

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. Registered User
Join Date
Mar 2004
Posts
2
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.