Counting with a hitch - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Counting with a hitch

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-10-10 : 20:13:33

Ok, I have a view that shows me the following:


ID    Ethnicity   Gender   Age
--    ---------   ------   ---
101   Asian       Male      24
102   White       Male      18
113   White       Unknown   35
108   Spanish     Female    67
234   Black       Female    24
345   Hispanic    Male      85
.     .           .         .
.     .           .         .
.     .           .         .

Now my Problem:
I need to do the following:
For each Age Range (There's 5 of them:
16-18, 19-24, 25-44, 45-59, 60 and over)
1. Take Each Ethnicity and count the number of ID's for each age range
2. Take that result and count the Males/Females

Now the view gives me all the data I'll be using, but I'd like to stay away from cursors/temp tables. My efforts thus far have been unsatisfactory. I have the problem broken down, but I have no idea about the SQL code that will give me the result I need.

Any help would be greatly appreciated.
--Nick

dshelton
Yak Posting Veteran

73 Posts

Posted - 2006-10-10 : 21:02:09

It be a good idea to have these ranges stored somewhere instead of in the code but if that's the way it has to be then try this:

SELECT Ethnicity, Description, Gender, COUNT(*) FROM view t
JOIN (SELECT 1 AS ID_Col, 16 AS AgeFrom, 18 AS AgeTo, '16-18' AS Description
UNION
SELECT 2, 19, 24, '19-24'
UNION
SELECT 3, 25, 44, '25-44'
UNION
SELECT 4, 45, 59, '45-59'
UNION
SELECT 5, 60, 200, '60+') AS Range
ON t.Age >= Range.AgeFrom
AND t.Age <= Range.AgeTo
GROUP BY Ethnicity, Description, Gender

Dave

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-10-10 : 21:37:23

It would be possible to store the ages somewhere else, what did you have in mind exactly?
--Nick
Oh and thanks for the query, looks good so far.

dshelton
Yak Posting Veteran

73 Posts

Posted - 2006-10-10 : 21:56:45

Create a table containing the ranges, then use the following query:

SELECT Ethnicity, Description, Gender, COUNT(*) FROM view t
JOIN Range
ON t.Age >= Range.AgeFrom
AND t.Age <= Range.AgeTo
GROUP BY Ethnicity, Description, Gender

Dave

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-10-10 : 22:07:44

Nice, clean. Thanks again.
--Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources