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
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.
这应该给你没有学生的团体,以及毕业生和本科生的数量。 (我将内部更改为左连接)。总和的想法来自马修琼斯,所以如果这对你有帮助,请给他一个投票。然后,您可以在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
0
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
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.
这应该给你没有学生的团体,以及毕业生和本科生的数量。 (我将内部更改为左连接)。总和的想法来自马修琼斯,所以如果这对你有帮助,请给他一个投票。然后,您可以在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
0
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