|
Home > Archive > Microsoft SQL Server forum > November 2005 > Need help with Count function and temporary tables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Need help with Count function and temporary tables
|
|
| Danielle 2005-11-21, 8:24 pm |
| 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 2005-11-21, 8:24 pm |
| 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 2005-11-21, 8:24 pm |
| your problem w/ your query is you do not want to group by the age =)
| |
| Danielle 2005-11-21, 8:24 pm |
| 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 2005-11-21, 8:24 pm |
| 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
|
|
|
|
|