We help IT Professionals succeed at work.

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

mixke1
mixke1 asked
on
Medium Priority
574 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

SharathData Engineer
CERTIFIED EXPERT

Commented:
Can you post some sample data with expected result.

Author

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

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Data Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Very Helpful
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
glad we could help

Author

Commented:
Thank you very much for your help!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.