Need help with Count function and temporary tables

[Danielle]

I have data like this in a two column temporary table -

ID Age

23586 3
23586 3
23586 2
23586 2
23586 1
23586 1
23586 1
23586 1
23586 1

I need to create a temporary table that look like this:

ID Age1 Age2 Age3 Age4

23586 5 2 2 0

However, what I get is this:

23586 5 NULL NULL NULL
23586 NULL 2 NULL NULL
23586 NULL NULL 2 NULL

Here is the query that I am using...
select managed_object_id, (select count(Age) where Age = 1) As Age1,
(select count(Age) where Age = 2) as Age2,
(select count(Age) where Age = 3) as Age3,
(select count(Age) where Age = 4) as Age4
into #enhancementCount from #enhancements
group by managed_object_id, Age

Where's my mistake?

Thanks-
Danielle

[getinked]

declare @v table (ids int, age int)

INSERT INTO @v (ids, age)
VALUES (23586, 3)
INSERT INTO @v (ids, age)
VALUES (23586, 3)
INSERT INTO @v (ids, age)
VALUES (23586, 2)
INSERT INTO @v (ids, age)
VALUES (23586, 2)
INSERT INTO @v (ids, age)
VALUES (23586, 1)
INSERT INTO @v (ids, age)
VALUES (23586, 1)
INSERT INTO @v (ids, age)
VALUES (23586, 1)
INSERT INTO @v (ids, age)
VALUES (23586, 1)
INSERT INTO @v (ids, age)
VALUES (23586, 1)
SELECT v.ids
, COUNT(CASE WHEN age = 1 THEN 1 END) AS Age1
, COUNT(CASE WHEN age = 2 THEN 1 END) AS Age2
, COUNT(CASE WHEN age = 3 THEN 1 END) AS Age3
, COUNT(CASE WHEN age = 4 THEN 1 END) AS Age4
FROM @v v
GROUP BY ids

[getinked]

your problem w/ your query is you do not want to group by the age =)

[Danielle]

getlinked -

You are a dream! Can you explain the logic a bit? Why does the CASE
statement work ? And is the implied ELSE of the CASE statements the '0'
that I see in the output table?

Thanks! :-)

Danielle

[getinked]

Its not so much in the case statement. The group by clause is where
the logic was out of place in your query. if you add the age field to
the group by in my query you will get the result set you had previous.
In the case statement if you dont specify a value for the other records
it just skips them, but the way i wrote it, if yoiu have say age 19 you
will get two records so you have to specify all the ages. if you want
an all inclusive statement to cover x age you would have to write it a
little different.

cheers