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.
| Author |
Topic |
|
willystyle04
Starting Member
3 Posts |
 Posted - 2010-05-19 : 09:42:30
|
New to the board, but am in need of some help with what seems entirely possible but I just can't translate my idea in to SQL (I'm a .NET developer and I can't seem to get away from wanting a loop, which I know is a no-no in SQL if it can be avoided). Here's the issue:-Only 1 table, called "Enroll" (I know, I'm not even joining to anything, this should be easy!)-Important fields: EnrollID(PK), Attend, ActivityID-Attend has string values, two I'm concerned with particularly (WTLT and ENRL)-I need to count WTLT and ENRL rows for each activityID (of which there are multiple rows)Here's the code for a SINGLE activity:DECLARE @ActivityID nchar(10);SET @ActivityID = 'XXXXXXX';SELECT ActivityID,(SELECT COUNT(Attend) FROM Enroll WHERE Attend = 'ENRL') As Enrolled, (SELECT COUNT(Attend) FROM Enroll WHERE Attend = 'WTLT') As WaitlistedFROM EnrollWHERE ActivityID = @ActivityID What I need to figure out is how to have it print an entire list, and do the count for each unique activity in the table. I can't figure out if there is a way to reference ActivityID in the first select as a variable in the two sub-selects. This seems like it should be simple to do, so it's quite frustrating! Here's what I would want as an output:ActivityID -- Enrolled -- WaitlistedXXXXXXX -- 4 -- 0XXXXXX -- 10 -- 5XXXXXXXX -- 6 -- 0 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-19 : 09:48:23
|
Try:selectActivityID,sum(case when Attend='ENRL' then 1 else 0 end) as Enrolled,sum(case when Attend='WTLT' then 1 else 0 end) as Waitlistedfrom Enrollgroup by ActivityID
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Need Help with Query...is this possible?