Results 1 to 2 of 2

Thread: GROUP BY Clause

  1. #1
    Join Date
    Oct 2002
    Posts
    11

    GROUP BY Clause

    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 !

  2. #2
    Join Date
    Sep 2002
    Posts
    169
    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

Posting Permissions

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