How about using GROUP BY, on a column defined using CASE?

Notes:
-- a CASE statement stops evaluating at the first WHEN that is true.
-- "GROUP BY 1" means use the contents of column 1

For example:

SELECT
AgeBracket_GROUP =
CASE
WHEN AGE IS NULL OR AGE = 0
THEN 0

WHEN AGE < 18
THEN 1

WHEN AGE <= 24
THEN 2

WHEN AGE <= 25
THEN 3

WHEN AGE <= 30
THEN 4

WHEN AGE <= 44
THEN 5

WHEN AGE <= 49
THEN 6

ELSE 9
END,
AgeBracket_LITERAL =
CASE
WHEN AGE IS NULL OR AGE = 0
THEN 'UNKNOWN AGE'

WHEN AGE < 18
THEN '<18'

WHEN AGE <= 24
THEN '18-24'

WHEN AGE <= 25
THEN '25-30'
.
.
.
ELSE '50+'
END,
JWBlack,
JWRed
GROUP BY 1
ORDER BY 1




Or, if you prefer more explicitly self-documenting code:

SELECT
AgeBracket_GROUP =
CASE
WHEN AGE IS NULL OR AGE = 0
THEN 0

WHEN AGE < 18
THEN 1

WHEN AGE <= 24
THEN 2

WHEN AGE <= 25
THEN 3

WHEN AGE <= 30
THEN 4

WHEN AGE <= 44
THEN 5

WHEN AGE <= 49
THEN 6

ELSE 9
END,
AgeBracket_LITERAL =
CASE
WHEN AGE IS NULL OR AGE = 0
THEN 'UNKNOWN AGE'

WHEN AGE < 18
THEN '<18'

WHEN AGE <= 24
THEN '18-24'

WHEN AGE <= 25
THEN '25-30'
.
.
.
ELSE '50+'
END,
JWBlack,
JWRed
GROUP BY CASE
WHEN AGE IS NULL OR AGE = 0
THEN 0

WHEN AGE < 18
THEN 1

WHEN AGE <= 24
THEN 2

WHEN AGE <= 25
THEN 3

WHEN AGE <= 30
THEN 4

WHEN AGE <= 44
THEN 5

WHEN AGE <= 49
THEN 6

ELSE 9
END
ORDER BY CASE
WHEN AGE IS NULL OR AGE = 0
THEN 0

WHEN AGE < 18
THEN 1

WHEN AGE <= 24
THEN 2

WHEN AGE <= 25
THEN 3

WHEN AGE <= 30
THEN 4

WHEN AGE <= 44
THEN 5

WHEN AGE <= 49
THEN 6

ELSE 9
END



-----Original Message-----
From: Chris Beukes
[mailto:mssqldba-ezmlmshield-x99491997.[Email address protected]
Sent: Monday, March 31, 2008 8:30 AM
To: LazyDBA Discussion
Subject: Please help

Hi SQL Guru's





Please help me.



I need to use a single query to get the following results sets:



AgeBracket JWBlack
JWRed

Unknown Age 18
3

<18 2
9

18-24 9
11

25-30 0
45

31-44 49
7

45-49 78
9

50+ 4
9



This specify the count of consumers per age group. JWBlack and JWRed is
group by filters.





Thanks



Chris Beukes

DBA

Global Vision





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page