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.

score 2 · Answer 1 · edited May 23 '17 at 12:03

2

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.

edited May 23 '17 at 12:03

Community

1
1

answered May 01 '11 at 20:26

John Hoven

4,085
2
28
32

1

You can't use a `WHERE` clause to fulfil the GRD/UGRD filter requirement because you don't know the aggregate values until after the grouping, but you can use a `HAVING` clause instead: `HAVING SUM(CASE WHEN CAREER = 'GRD' THEN 1 ELSE 0 END) > SUM(CASE WHEN CAREER = 'UGRD' THEN 1 ELSE 0 END)`. (I'd lose all the double-quotes, but if they're included then the column and table names have to match case with the definition; and the double-quotes around the column values should be single-quotes anyway). – Alex Poole May 01 '11 at 22:11
You should mention that one can add the `HAVING Grads > UGrads` clause in the end (and not a `WHERE`). – ypercubeᵀᴹ May 01 '11 at 22:47
@Alex I knew the quotes felt wrong when I was typing it, thanks. Left the rest how the question had it. Fixed mention of where to having. Thanks, its been awhile since I've used it. – John Hoven May 02 '11 at 02:01

score 0 · Answer 2 · answered May 01 '11 at 22:54

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

need help on an SQL query

2 Answers2