Help!

gfowajuh · August 18th, 2003, 08:51 AM

How do i count the ages of patients in table1 to produce the groupings as indicated in Table2 using SQL Server? I used to be able to do this in ACCESS using IIF. Please help

Table1 Table2
PatientID Age AgeGroup Totals
1 10 0-4 3
2 12 5-9 0
3 2 10-14 3
4 3 15-19 2
5 100 20-24 3
6 23 25 Plus 1
7 22
8 20
9 17
10 15
11 1
12 10

Jeff Mason · August 18th, 2003, 09:40 AM

You can sort of get what you want with a somewhat ugly use of the CASE expression:

Code:

SELECT CASE WHEN age BETWEEN 0 AND 4 THEN '0-4'
    WHEN age BETWEEN 5 AND 9 THEN '5-9'
    WHEN age BETWEEN 10 AND 14 THEN '10-14'
    WHEN age BETWEEN 15 AND 19 THEN '15-19'
    WHEN age BETWEEN 20 AND 24 THEN '20-24'
    ELSE '25 plus' END AS AgeGroup,
    COUNT(*) AS Totals
FROM YourTable
GROUP BY CASE WHEN age BETWEEN 0 AND 4 THEN '0-4'
    WHEN age BETWEEN 5 AND 9 THEN '5-9'
    WHEN age BETWEEN 10 AND 14 THEN '10-14'
    WHEN age BETWEEN 15 AND 19 THEN '15-19'
    WHEN age BETWEEN 20 AND 24 THEN '20-24'
    ELSE '25 plus' END;

but this will not return any groups with 0 members (e.g. group '5-9').

A better solution might be to create a table which defines the groups you are interested in. Perhaps a table like:

Code:

CREATE TABLE Intervals (
    LoRange int NOT NULL PRIMARY KEY,
    HiRange int NOT NULL ,
    Descr char(10) NOT NULL);

and populate it appropriately. This has the advantage of making your grouping considerably more flexible. Pick a suitably high value for the 'HiRange' of the last group. Then the query might be:

Code:

SELECT descr AS AgeGroup, COUNT(age) as totals
FROM Intervals LEFT JOIN YourTable ON YourTable.Age BETWEEN Intervals.LoRange AND Intervals.HiRange
GROUP BY descr

This of course begs the question of why you have a dependent value like 'Age' stored in your table in the first place. The value is dependent because an age is always relative to the day on which you compute it. Your design means that you will have to have some process which runs periodically which updates the 'Age' column. If you forget to run it, your data is corrupt. Not good. Better to store the date of birth and compute the age whenever you need it.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

melvik · August 18th, 2003, 10:07 AM

Jeff was correct but I have something dinamic!!!

Code:

DECLARE @Min smallint
    ,@Max smallint
    ,@Range smallint
SET @Min = 0
SET @Max = 25
SET @Range = 4

SELECT CASE 
    WHEN Age >= @Max THEN @Max+@Range
    ELSE ((Age-@Min)/@Range)*@Range+@Min END AS Sort
    ,CASE    WHEN Age < @Min THEN 'Less Than '+'0'+'ÃÃÃ¡ '
        WHEN Age >= @Max THEN 'More than '+ CAST(@Max AS varchar(3))
        ELSE CAST(((Age-@Min)/@Range)*@Range+@Min AS varchar(10))+'-'+CAST(((Age-@Min)/@Range)*@Range+@Min+@Range AS varchar(10)) END AS AgeGroup
    ,Count(Age) AS [Count]
FROM YourTable
GROUP BY CASE 
    WHEN Age >= @Max THEN @Max+@Range
    ELSE ((Age-@Min)/@Range)*@Range+@Min END
    ,CASE    WHEN Age < @Min THEN 'Less Than '+'0'+'ÃÃÃ¡ '
        WHEN Age >= @Max THEN 'More than '+ CAST(@Max AS varchar(3))
        ELSE CAST(((Age-@Min)/@Range)*@Range+@Min AS varchar(10))+'-'+CAST(((Age-@Min)/@Range)*@Range+@Min+@Range AS varchar(10)) END
ORDER BY Sort

Always:),
Hovik Melkomian.

Jeff Mason · August 18th, 2003, 10:20 AM

Very

.

Works as long as the age range is the same for all intervals...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

gfowajuh · August 19th, 2003, 05:13 AM

Thanks guys for the solutions.
George