需要SQL查询的帮助

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)

职业生涯是“ugrd”或“grd”的学生(SID,职业),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.

这是我迄今为止工作的代码,但它只列出了包含学生的组。我需要一个查询,可以列出比“UGRD”更多“GRD”学生的小组,但我不确定如何比较每个小组中的本科生和毕业生的数量,并选择具有更多毕业生的小组。

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.

到目前为止,我已尝试使用count函数,但无法找到一种方法来操作它以回答问题的方式。我真的想了解如何获得我需要的结果以及它是如何工作的。如果有人可以提供帮助,我真的很感激。谢谢。

2 个解决方案

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

这应该给你没有学生的团体,以及毕业生和本科生的数量。 (我将内部更改为左连接)。总和的想法来自马修琼斯,所以如果这对你有帮助,请给他一个投票。然后,您可以在Grads和UGrads列上执行having子句。

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.

编辑:根据评论修复。

#2

The HAVING condition could be written as:

HAVING条件可写为:

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

#1

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