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)
SELECT TRUNC(AGE, -1) AS GROUP0,
(COUNT0 * AGE) AS SUM_AGE,
((COUNT0 * AGE) / COUNT0) AVG_AGE
SELECT COUNT(*) AS COUNT0 , AGE
GROUP BY AGE)
GROUP BY GROUP0
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 (
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'
select x.age_group ,count(*) ,avg(age)
group by x.age_group