MSSQL query-count if 'N' display if 'Y'

I need a query that will display:
crs_name
usr_name    ---- (if crs_creator = 'y')
count(usr_id) -- (if crs_creator ='n')

database structure

user_course
  uc_id
  crs_id
  crs_creator
  usr_id

user
usr_id
usr_name
usr_phone
usr_address

course
crs_id
crs_name
crs_start

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.
mixke1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
SharathData EngineerCommented:
Can you post some sample data with expected result.
0
 
mixke1Author Commented:
USERS
USR_ID|USR_NAME|USR_PHONE |USR_ADDRESS
1           |Bob              |111-111-1111   |
2           |Steve           |222-222-2222 |
3           |Scott            |333-333-3333 |

COURSES
CRS_ID|CRS_NAME|CRS_START
1           |Course1     |1-1-2011
2           |Course2     |5-10-2011
3           |Course3     |1-20-2011

USER_COURSE
UC_ID|CRS_ID|CRS_CREATOR|USR_ID
1        |1            |Y                         |1
2        |1            |N                         |2
3        |1            |N                         |3
4        |2            |Y                         |3
5        |2            |N                         |1
6        |3            |Y                         |2


The result I am looking for is
Name    | Creator | Count
Course1| Bob      | 2
Course3| Steve   | 0
Course2| Scott    | 1

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
SELECT Courses.Crs_Name as 'Course Name'
 , max( case when dbo._User_Course.Crs_Creator = 'y' THEN (Users.Usr_FName + ' ' + Users.Usr_LName) ELSE NULL END ) as Professor
 , sum (CASE WHEN User_Course.Crs_Creator = 'n' THEN 1 ELSE 0 END ) as Enrollment
FROM User_Course 
JOIN Courses ON User_Course.Crs_ID = Courses.Crs_ID
JOIN Users ON Users.Usr_ID = User_Course.Usr_ID
GROUP BY Courses.Crs_Name 
ORDER BY max(Courses.Crs_Start) ASC

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
SharathData EngineerCommented:
try this.
SELECT c.CRS_NAME Name, 
         MAX(CASE uc.CRS_CREATOR 
               WHEN 'Y' THEN u.USR_NAME 
             END) Creator, 
         SUM(CASE uc.CRS_CREATOR 
               WHEN 'N' THEN 1 
               ELSE 0 
             END) [Count] 
    FROM USER_COURSE uc 
         JOIN USERS u 
           ON uc.USR_ID = u.USR_ID 
         JOIN COURSES c 
           ON uc.CRS_ID = c.CRS_ID 
GROUP BY c.CRS_NAME

Open in new window

tested with your sample.
declare @USERS table (USR_ID int,USR_NAME varchar(100))
declare @COURSES table (CRS_ID int,CRS_NAME varchar(100))
declare @USER_COURSE table(CRS_ID int,CRS_CREATOR varchar(5),USR_ID int)
 
insert @USERS values (1,'Bob'),(2,'Steve'),(3,'Scott')
insert @COURSES values (1,'Course1'),(2,'Course2'),(3,'Course3')
insert @USER_COURSE values (1,'Y',1),(1,'N',2),(1,'N',3),(2,'Y',3),(2,'N',1),(3,'Y',2)

SELECT c.CRS_NAME Name, 
         MAX(CASE uc.CRS_CREATOR 
               WHEN 'Y' THEN u.USR_NAME 
             END) Creator, 
         SUM(CASE uc.CRS_CREATOR 
               WHEN 'N' THEN 1 
               ELSE 0 
             END) [Count] 
    FROM @USER_COURSE uc 
         JOIN @USERS u 
           ON uc.USR_ID = u.USR_ID 
         JOIN @COURSES c 
           ON uc.CRS_ID = c.CRS_ID 
GROUP BY c.CRS_NAME
/*
Name	Creator	Count
Course1	Bob	2
Course2	Scott	1
Course3	Steve	0
*/

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
mixke1Author Commented:
Very Helpful
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad we could help
0
 
mixke1Author Commented:
Thank you very much for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.