GROUP BY Clause

[aprdv]

Hi All,

I have a SQL statement which gives me groupings of my employees in their age group. I have a DOB column in my table and this is what I want my output to be like .......

AgeGroup EmployeeCount
--------- -------------
0 - 10 yrs 0
11 - 20 yrs 2
21 - 30 yrs 29
31 - 40 yrs 21
41 - 50 yrs 15
51 - 60 yrs 9
61 - 70 yrs 3
> 70 yrs 0

But here is what I am getting .......

AgeGroup EmployeeCount
--------- -------------
11 - 20 yrs 2
21 - 30 yrs 29
31 - 40 yrs 21
41 - 50 yrs 15
51 - 60 yrs 9
61 - 70 yrs 3

I have two rows missing, 0-10 yrs and > 70 yrs age groups. This is happening since I don't have any employees falling into those categories. How do I force my query to display zeros for these categories. Any amount of help will be grately appreciated.

Thanks !

[Stephen]

Create a table with the age ranges you want to report on and left join to it

e.g.
SET NOCOUNT ON
CREATE TABLE MyData (Age int)
CREATE TABLE MyAgeRange (LowerAge int,
UpperAge int,
AgeRange VARCHAR (20))
go
INSERT INTO MyData VALUES (1)
INSERT INTO MyData VALUES (11)
INSERT INTO MyData VALUES (12)
INSERT INTO MyData VALUES (12)
INSERT INTO MyData VALUES (31)
INSERT INTO MyData VALUES (32)
INSERT INTO MyData VALUES (31)

INSERT INTO MyAgeRange
VALUES (0, 10, '0 - 10 years')
INSERT INTO MyAgeRange
VALUES (11, 20, '11 - 20 years')
INSERT INTO MyAgeRange
VALUES (21, 30, '21 - 30 years')

INSERT INTO MyAgeRange
VALUES (31, 40, '31 - 40 years')
INSERT INTO MyAgeRange
VALUES (41, 200, 'Over 40')

SELECT AgeRange, Count (MyData.Age)
FROM MyAgeRange
LEFT OUTER JOIN MyData ON MyData.Age BETWEEN MyAgeRange.LowerAge AND MyAgeRange.UpperAge
GROUP BY AgeRange