need help on an SQL query

Question

I've been having some difficulty with a query. What I have are two tables(irrelevant data omitted):

students(SID, career) where career is either "ugrd" or "grd",
memberof(studentid, groupname)

 SELECT "GROUPNAME" FROM "MEMBEROF"
 INNER JOIN "STUDENT"      //to get rid of students not in a group
 ON "SID" = "STUDENTID"
 GROUP BY "GROUPNAME"      //so that no duplicates are listed

this is the code I have that works so far but it only lists groups that have students in them. I need a query that can list groups with more "GRD" students than "UGRD", but I am unsure how to go about comparing the number of undergrads vs. grads in each group and selecting the groups with more grads.

So far, I have tried working with the count function but have been unable to figure out a way to manipulate it in a way that would answer the problem. I really want to understand how to get the results I need as well as how it works. If anyone could help, i'd really appreciate it. Thanks.

Answer 1

This should give you groups with no students, and the counts of grads and undergrads. (I changed the inner to a left join). The sum idea comes from Matthew Jones, so please go give him an up-vote if this helps you too. You can then do a having clause on the Grads and UGrads columns.

SELECT "GROUPNAME", 
SUM(CASE WHEN career= 'grd' THEN 1 ELSE 0 END) AS 'Grads',
SUM(CASE WHEN career = 'ugrd' THEN 1 ELSE 0 END) AS 'UGrads' 
FROM "MEMBEROF"
LEFT JOIN "STUDENT"      //to get rid of students not in a group, but keep groups with no students
 ON "SID" = "STUDENTID"
 GROUP BY "GROUPNAME"      //so that no duplicates are listed
HAVING Grads > UGrads 

EDIT: Fixed based on comments.

Answer 2

The HAVING condition could be written as:

HAVING SUM(CASE WHEN CAREER = 'GRD' THEN 1 ELSE 0 END)
     > SUM(CASE WHEN CAREER = 'UGRD' THEN 1 ELSE 0 END)

or like this:

HAVING SUM(CASE WHEN CAREER = 'GRD'
                  THEN 1
                WHEN CAREER = 'UGRD'
                  THEN -1
                ELSE 0 END)
       > 0