How to get a static result from a table?
-
I have a table in SQL Server 2008 like this: EventID UserID State 1 0987 A 2 0987 A 3 0987 B 4 0956 A 5 0987 A 6 0019 A 7 0019 B 8 1289 B 9 1289 B 10 0019 A I want to get some results from this table in such form: UserID StateCount_of_A StateCount_of_B 0987 3 1 0019 2 1 1289 0 2 0956 1 0 I have try a few days, but I could get a disappointed result for ony one UserID. My result likes this one, for example with UserID 0987: StateCount_of_A 3 StateCount_of_B 1 Thanks for anyone who could give me some tip.
-
I have a table in SQL Server 2008 like this: EventID UserID State 1 0987 A 2 0987 A 3 0987 B 4 0956 A 5 0987 A 6 0019 A 7 0019 B 8 1289 B 9 1289 B 10 0019 A I want to get some results from this table in such form: UserID StateCount_of_A StateCount_of_B 0987 3 1 0019 2 1 1289 0 2 0956 1 0 I have try a few days, but I could get a disappointed result for ony one UserID. My result likes this one, for example with UserID 0987: StateCount_of_A 3 StateCount_of_B 1 Thanks for anyone who could give me some tip.
Try ... Select EventID,(select count(*) from E2 where E1.EventID = E2.EventID) As Count_of_A, (select count(*) from E3 where E1.EventID = E3.EventID) As Count_of_B, from E1 Remeber to Vote. :cool:
-
I have a table in SQL Server 2008 like this: EventID UserID State 1 0987 A 2 0987 A 3 0987 B 4 0956 A 5 0987 A 6 0019 A 7 0019 B 8 1289 B 9 1289 B 10 0019 A I want to get some results from this table in such form: UserID StateCount_of_A StateCount_of_B 0987 3 1 0019 2 1 1289 0 2 0956 1 0 I have try a few days, but I could get a disappointed result for ony one UserID. My result likes this one, for example with UserID 0987: StateCount_of_A 3 StateCount_of_B 1 Thanks for anyone who could give me some tip.
I haven't tried this, however my first try would look like this:
SELECT DISTINCT UserID, StateCount_of_A, StateCount_of_B FROM tablename AS T1
LEFT JOIN (SELECT UserID, COUNT(*) AS StateCount_of_A FROM tablename AS TA WHERE State='A') ON TA.UserID = T1.UserID
LEFT JOIN (SELECT UserID, COUNT(*) AS StateCount_of_B FROM tablename AS TB WHERE State='B') ON TB.UserID = T1.UserID
ORDER BY UserID(Actually I would try a subset of it with a single state first, then expand for the exact requirements). The fundamental trick is to use the same table more than once, and give each "instance" a different name using the AS keyword. And then one might have to throw in a couple of COALESCE functions to replace null counts by zeroes. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
I have a table in SQL Server 2008 like this: EventID UserID State 1 0987 A 2 0987 A 3 0987 B 4 0956 A 5 0987 A 6 0019 A 7 0019 B 8 1289 B 9 1289 B 10 0019 A I want to get some results from this table in such form: UserID StateCount_of_A StateCount_of_B 0987 3 1 0019 2 1 1289 0 2 0956 1 0 I have try a few days, but I could get a disappointed result for ony one UserID. My result likes this one, for example with UserID 0987: StateCount_of_A 3 StateCount_of_B 1 Thanks for anyone who could give me some tip.
SELECT UserId
,SUM(StateA) StateCount_of_A
,SUM(StateB) StateCount_of_B
FROM (
SELECT UserId
,CASE WHEN [State]='A' THEN 1 ELSE 0 END StateA
,CASE WHEN [State]='B' THEN 1 ELSE 0 END StateB
FROM Junk
) T
GROUP BY UserID -
Try ... Select EventID,(select count(*) from E2 where E1.EventID = E2.EventID) As Count_of_A, (select count(*) from E3 where E1.EventID = E3.EventID) As Count_of_B, from E1 Remeber to Vote. :cool:
You seemed to have picked up a stalker or someone objects to the unformatted code or you are getting a reaction to your sig!
Never underestimate the power of human stupidity RAH