SQL Query - 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)

SQL Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-23 : 09:12:49

Mousumi writes "I am struggling hard to get the desired output..

Consider the table structure
tblCensus
---------
Name
DOB
Sex

a) From the CENSUS table write a query which will give the output as below.

Age 11-20 Age 21-30 Age 31-50 Age > 50
-------- --------- --------- ---------
Males 1 2 7 9
Females 5 10 20 30

I am getting above output in scripts, more than single query, but is it possible to get the desired output in single query?

Thanks,
Mousumi"

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-23 : 09:52:52

This is by no means complete, the age calculation in particular isn't accurate and you should search for 'age' elsewhere in this forum, but it should give you a start:

SELECT gender,
SUM(CASE WHEN age < 20 THEN 1 ELSE 0 END) AS ageunder20,
SUM(CASE WHEN age BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS age2130,
SUM(CASE WHEN age BETWEEN 31 AND 50 THEN 1 ELSE 0 END) AS age3150,
SUM(CASE WHEN age >= 50 THEN 1 ELSE 0 END) AS age50plus
FROM
(
SELECT gender, DATEDIFF(yy, dob, GETDATE()) AS age
FROM @Census
) z
GROUP BY gender

Raymond

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-23 : 12:34:52

A couple of small changes:

SELECT gender,
SUM(CASE WHEN age <= 20 THEN 1 ELSE 0 END) AS ageunder20,
SUM(CASE WHEN age BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS age2130,
SUM(CASE WHEN age BETWEEN 31 AND 50 THEN 1 ELSE 0 END) AS age3150,
SUM(CASE WHEN age > 50 THEN 1 ELSE 0 END) AS age50plus
FROM
(
SELECT gender, DATEDIFF(yy, dob, GETDATE()) AS age
FROM @Census
) z
GROUP BY gender

To include 20 in the twenty and under and exclude 50 in the over 50. And note that BETWEEN is inclusive of the end points.

Roberta
Starting Member

2 Posts

Posted - 2004-02-23 : 14:43:20

This will give the person's exact age.

(DATEDIFF(dd, dob, GETDATE())/365.25) AS age

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources