help with select query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with select query

Author

Topic

kifeda
Posting Yak Master

136 Posts

Posted - 2006-10-01 : 15:34:05

Hello All!

I am trying to return a recordset that has the total amount of enrolled students per program within a selected date range, for a selected semester. Here is the select statement:

SELECT COUNT(DISTINCT userid) AS Totalregistrants,
l.classname as level,
c.classname as semester
FROM registration r
INNER JOIN classes l ON l.classid = r. LEVEL
INNER JOIN classes c ON c.classid = r.semester
WHERE r.date BETWEEN 'var1' AND 'var2' and r.semester like 'vars' and r.registered = 2
GROUP BY LEVEL, l.classname, c.classname

This works fine. This gives me a recordset like this:

(it also gives me the semester, but it was necessary to show in the example)

-----------------------------
Level Enrolled
-----------------------------
Avanced Level 34
Beinner Level 50
Intermediate Level 37

However, now I need the total number of enrolled students AND students who have withdrawn from the semester. The field 'registered' inside the registration table tells me if there are enrolled (2) or withdrawn (3). How can I return a recordet set like this:

---------------------------------------------
Level Enrolled Withdrawn
---------------------------------------------
Avanced Level 34 5
Beinner Level 50 10
Intermediate Level 37 3

I thought may be there was a way to use a select statement within a select statement, but then the group by clause would not work. Please help!

Thank you in advance.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-01 : 17:58:27

This should do it.

SELECT SUM(CASE WHEN r.registered = 2 THEN 1 ELSE 0 END) AS Totalregistrants, 
SUM(CASE WHEN r.registered = 3 THEN 1 ELSE 0 END) AS TotalWithdrawn, 
l.classname as [level], 
c.classname as semester
FROM registration r 
INNER JOIN classes l ON l.classid = r.[LEVEL] 
INNER JOIN classes c ON c.classid = r.semester
WHERE r.date BETWEEN 'var1' AND 'var2' and r.semester like 'vars' and r.registered in (2, 3)
GROUP BY LEVEL, l.classname, c.classname

kifeda
Posting Yak Master

136 Posts

Posted - 2006-10-01 : 19:03:22

thank you very much! it worked!

kifeda
Posting Yak Master

136 Posts

Posted - 2006-10-01 : 20:06:47

wait. actually that doesn't work. there really is not any distinct record for the registration of a student. instead what happens is that I insert into a table the userid, classid among other things, and then I select the distinct userid from the group of classes. so I have three or four records for each student in the registration table. One record for each class. When I want to see who is registered for the semester, I just select the distinct userid and that will return one record for each student. So the problem is when we count by registered what we get is the agregate count for the classes NOT necessarily for the students. So how do we get the count for the students and NOT the classes?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-02 : 23:37:17

I think this should fix that


SELECT SUM(CASE WHEN r.registered = 2 THEN 1 ELSE 0 END) AS Totalregistrants, 
SUM(CASE WHEN r.registered = 3 THEN 1 ELSE 0 END) AS TotalWithdrawn, 
l.classname as [level], 
c.classname as semester
FROM (SELECT [level], semester, registered 
    FROM registration
    WHERE [date] BETWEEN 'var1' AND 'var2' 
        and semester like 'vars' 
        and registered in (2, 3)
    GROUP BY userid, [level], semester, registered) r 
INNER JOIN classes l ON l.classid = r.[LEVEL] 
INNER JOIN classes c ON c.classid = r.semester
GROUP BY r.[LEVEL], l.classname, c.classname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources