MSSQL query-count if 'N' display if 'Y'
Posted on 2011-04-27
I need a query that will display:
usr_name ---- (if crs_creator = 'y')
count(usr_id) -- (if crs_creator ='n')
Here is what I have but its not grouping the NULLS so I'm getting an incorrect count.
SELECT Courses.Crs_Name as 'Course Name',
case when dbo._User_Course.Crs_Creator = 'y' THEN (Users.Usr_FName + ' ' + Users.Usr_LName) ELSE NULL END as Professor,
count(CASE WHEN User_Course.Crs_Creator = 'n' THEN User_Course.Crs_Creator ELSE NULL END ) as Enrollment
FROM User_Course INNER JOIN Courses ON User_Course.Crs_ID = Courses.Crs_ID
INNER JOIN Users ON Users.Usr_ID = User_Course.Usr_ID
GROUP BY Courses.Crs_Name, Users.Name, User_Course.Crs_Creator
ORDER BY Courses.Crs_Start ASC
I hope this makes sense to someone. I'm a total MS-SQL NEWBIE.