Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

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.
0
mixke1
Asked:
mixke1
  • 3
  • 2
  • 2
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now