SQL Help! - Counting values in a field

G

Gambit

Gawd
Joined
Aug 26, 2002
Messages
764
This is kind of hard to describe, but I'll do my best. Assume you have a class registration table with the important fields being
  • RegID - Autoincrement
  • ClassID - non-unique integer
  • RegStatus - non-unique integer

RegID is the unique identifier for each row, ClassID is the ID number for the class, RegStatus shows if the registrant is registered (1), on a wait list (2), or not registered at all (0). What I need to do is get a list of the classes with how many users are registered and wait listed. For example:

Code: 
RegID -- ClassID -- RegStatus
-------------------------------------
1            1         1
2            1         1
3            2         1
4            1         2
5            3         1
5            3         2

The type of output I'm looking for is:

Code: 
ClassID -- Registerd  -- WL
1               3        1
2               1        0
3               1        1

Basically, I need to know how many 1's and how many 2's there are in the RegStatus column for each ClassID. I could've swore I've done this before, but for some reason I seem to be pulling my hair out on this..
 
yes, but afaik I can only use count to count a field non-conditionally.

For instance, trying something like:
Code: 
select ClassID, count(RegStatus) from Classes;

will get me something completely different than what I want. Please look at the examples I described in my first post; I need to get the number of 1's and number of 2's for each class.

Code: 
select ClassID, count(RegStatus) from Classes where RegStatus=1 group by ClassID;

is very close to what I want... except it doesn't have the second RegStatus column for the count of how many are #2 (Wait listed). Obviously, I could run another statement, but when you're trying to output to a website:

<classname> <registered> <wait listed> and having to have php code to figure things out... it's just more work than it should be.
 
Last edited:
I don't think there's any simple way to do this, since you're trying to aggregate the same column into two separate outputs. If someone smarter than me comes up with a way using simple operations, I will be duly impressed, but I think this will require subselects. This does what you want, but it's not exactly pretty or efficient and there's probably a better way:

Code: 
SELECT DISTINCT ClassID,
  (SELECT COUNT(RegId) FROM classes AS i WHERE i.ClassId=o.ClassId and RegStatus=1),
  (SELECT COUNT(RegId) FROM classes AS i WHERE i.ClassId=o.ClassId and RegStatus=2)
FROM classes AS o;
 classid | ?column? | ?column?
---------+----------+----------
       1 |        2 |        1
       2 |        1 |        0
       3 |        1 |        1
(3 rows)

However, if possible it should be more efficient to create a separate column for registered and waitlisted and do thus:
Code: 
create table classes (id serial primary key, classid int, regd bool, wl bool);
select distinct classid, sum(regd::int), sum(wl::int) from classes group by classid;
 classid | sum | sum
---------+-----+-----
       1 |   2 |   1
       2 |   1 |   0
       3 |   1 |   1
(3 rows)

Postgres syntax here, you haven't specified a DBMS, but the concepts should translate easily.
 
You can use a SUM over a conditional. The syntax for this varies depending on which SQL implementation you're using.

For T-SQL in SQL Server, it's something like:

Code: 
SELECT ClassID,
	SUM(CASE WHEN RegStatus = 1 THEN 1 ELSE 0 END) AS Registered,
	SUM(CASE WHEN RegStatus = 2 THEN 1 ELSE 0 END) AS WaitListed
FROM YourTable
GROUP BY ClassID

In MySQL, you use the "IF" statement similarly.
 
keenan said:
However, if possible it should be more efficient to create a separate column for registered and waitlisted and do thus:
Click to expand...

I thought about that, but having a separate colum didn't make sense. This particular table lists each registrant and *how* they're registered (if at all). I *could* also update the Classes table to keep a tally of how many are registered or waitlisted, but since I more or less have that same information available, I was hoping to get it this way. I suppose it's a difference between having conditionals in the select statement or dealing with inserting extra columns in each class row each time someone registers.

mikeblas said:
You can use a SUM over a conditional. The syntax for this varies depending on which SQL implementation you're using.
Click to expand...

I'll look into that. The command you provided looks exactly like what I'm looking for. The benefit is that I *am* using MYSQL, so I even get to do some homework and learn a bit on my own :cool:


Much appreciated guys.
 
Well, the example I gave was a generalization. I actually need to pull class names (which are in another table) and account for no one being registered yet. So there's still a bit to play with, even though the overall idea works.
 
Gambit said:
Well, the example I gave was a generalization. I actually need to pull class names (which are in another table) and account for no one being registered yet. So there's still a bit to play with, even though the overall idea works.
Click to expand...

You're just a JOIN away! :p
 
Yeah, that's what I was thinking... still not working.

Code: 
select 
  left(C.ClassName, 65) as ClassName, 
  sum(case when R.RegStatus = 1 then 1 else 0 end) as Registered, 
  sum(case when R.RegStatus = 2 then 1 else 0 end) as WaitList 
from 
  Classes C
left join
  Registered R 
on 
  C.ClassID = R.ClassID 
group by 
  R.ClassID;

actually returns one class with 0 for registered and wait list, even though there's nothing in the registered table at all (why only return one row?). Trying to sort out if it's because I have the sum() function and no data really being passed to it...
 
It returns one row because that's what you asked for when you specified an outer join.
 
For clarification, a left join is considered an "outer join" because it lists all the items on the left table, then matches them where it can with the right table. An "inner join" would only return rows where there were matches.
 
So far, it looks like it was because sum was returning null... only thing I changed was

Code: 
sum(case when R.RegStatus = 1 then 1 else 0 end) as Registered

to

Code: 
sum(coalesce(case when R.RegStatus = 1 then 1 else 0 end,0)) as Registered

and the same with WaitList
 
SUM was returning null because you asked for null rows when you specified an outer join.
 
You must log in or register to reply here.
Back
Top