November 8, 2002 at 12:50 am
Hi,
Anyone out there who can help me with the following:
Given a view Members with has columns Age and Number. The view reports for Age 21 - 30 the rows:
Age Number
21 2
22 3
24 6
25 6
26 6
27 4
28 3
30 4
As you see there are no members with age 23 and 29.
I want to have a query with reports also Age 23 and 29 both with Number = 0 (eq all missing Ages between 0 and max(Age)) like:
Age Number
21 2
22 3
23 0
24 6
25 6
26 6
27 4
28 3
29 0
30 4
Any Ideas how to create such a query?
Many Thanks
Piet
November 8, 2002 at 2:14 am
Its a bit lengthy but it does work.
declare @LowerAge int
declare @UpperAge int
set @LowerAge = 21
set @UpperAge = 30
declare @zerocount table (Age int, Qty int)
while @LowerAge <= @UpperAge
begin
insert into @zerocount (Age, Qty) values (@LowerAge, 0)
set @LowerAge = @LowerAge + 1
end
select Age, sum(Qty) as Qty
from ( select Age, count(*) as Qty
from members
group by Age
union
select Age, Qty
from @zerocount) results
group by Age
Perhaps someone else will come up with a simpler method.
November 8, 2002 at 5:52 am
Maybe make a permanent control table with all the age values 1 - 120 (I doubt anyone over 120), especially if this query will run more than once.
My query is different.
AgeCtrl table is Age column of datatype smallint with values 1-120.
SELECT
CASE WHEN Base.AGE IS NULL THEN AgeCtrl.Age ELSE Base.Age END Age,
CASE WHEN Base.AGE IS NULL THEN 0 ELSE Base.Number END Number
FROM
(
SELECT AGE, COUNT(*) Number FROM memebers GROUP BY AGE
) AS Base
RIGHT JOIN
AgeCtrl
ON
Base.Age = AgeCtrl.Age
November 8, 2002 at 6:05 am
This would work too, looks a little odd thou
SELECT
CASE WHEN Base.AGE IS NULL THEN AgeCtrl.Age ELSE Base.Age END Age,
CASE WHEN Base.AGE IS NULL THEN 0 ELSE Base.Number END Number
FROM
(
SELECT AGE, COUNT(*) Number FROM memebers GROUP BY AGE
) AS Base
RIGHT JOIN
(select a.ones + b.tens + c.hundreds Age from
(select 0 as ones UNION select 1 UNION select 2 UNION select 3 UNION select 4 UNION select 5 UNION select 6 UNION select 7 UNION select 8 UNION select 9) as a
join (select 0 as tens UNION select 10 as tens UNION select 20 UNION select 30 UNION select 40 UNION select 50 UNION select 60 UNION select 70 UNION select 80 UNION select 90) as b ON 1=1 -- Remove union select number for any tens group age you will never need.
join (select 0 as hundreds UNION select 100) as c ON 1=1 -- If you don't need to go above 99 remove this line
) As AgeCtrl
ON
Base.Age = AgeCtrl.Age
WHERE
AgeCtrl.Age BETWEEN 20 AND 30
ORDER BY AGE
November 8, 2002 at 7:23 am
Thanks for responding. This will help me a lot.
Many Thanks again
Piet
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply