SQL 计数列分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2698108/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
count columns group by
提问by e.b.white
I hava the sql as below:
我有如下 sql：
select a.dept, a.name
from students a
group by dept, name
order by dept, name
And get the result:
并得到结果：
dept name
-----+---------
CS | Aarthi
CS | Hansan
EE | S.F
EE | Nikke2
I want to summary the num of students for each dept as below:
我想总结每个部门的学生人数如下：
dept name count
-----+-----------+------
CS | Aarthi | 2
CS | Hansan | 2
EE | S.F | 2
EE | Nikke2 | 2
Math | Joel | 1
How shall I to write the sql?
sql应该怎么写？
回答by DRapp
Although it appears you are not showing all the tables, I can only assume there is another table of actual enrollment per student
虽然看起来您没有显示所有表格，但我只能假设还有另一个表格显示每个学生的实际入学人数
select a.Dept, count(*) as TotalStudents
from students a
group by a.Dept
If you want the total count of each department associated with every student (which doesn't make sense), you'll probably have to do it like...
如果您想要与每个学生相关联的每个部门的总数（这没有意义），您可能必须这样做......
select a.Dept, a.Name, b.TotalStudents
from students a,
( select Dept, count(*) TotalStudents
from students
group by Dept ) b
where a.Dept = b.Dept
My interpretation of your "Name" column is the student's name and not that of the actual instructor of the class hence my sub-select / join. Otherwise, like others, just using the COUNT(*) as a third column was all you needed.
我对您的“姓名”列的解释是学生的姓名，而不是班级实际讲师的姓名，因此我进行了子选择/加入。否则，与其他人一样，只需使用 COUNT(*) 作为第三列即可。
回答by Marcelo Cantos
select a.dept, a.name,
(SELECT count(*)
FROM students
WHERE dept = a.dept)
from students a
group by dept, name
order by dept, name
This is a somewhat questionable query, since you get duplicate copies of the department counts. It would be cleaner to fetch the student list and the department counts as separate results. Of course, there may be pragmatic reasons to go the other way, so this isn't an absolute rule.
这是一个有点可疑的查询，因为您会得到部门计数的重复副本。获取学生列表会更清晰，并且部门计为单独的结果。当然，可能有务实的理由走另一条路，所以这不是绝对的规则。
回答by souLTower
SELECT dept, name, COUNT(name) as CT from students
group by dept, name
order by dept, name
回答by OneSHOT
This should do it (I haven't got any environment to test on at the min)
这应该可以做到（我没有任何环境可以在分钟内测试）
select a.dept, a.name, count(a.*) as NumOfStudents
from students a
group by dept, name order by dept, name
HTH
HTH
回答by Karthik
Or Otherwise write simply
否则简单地写
select dept, name, count(name) as nostud from students group by dept, name order by dept, name
回答by BlackICE
This will give the results requested above
这将给出上面要求的结果
select a.dept, a.name, cnt
from student a
join (
select dept, count(1) as cnt
from student
group by dept
) b on b.dept = a.dept
