在SQL中按组。

i want to count after group by, not the total line just want to count by the categories after group by my result is like

我想一组接着一组的数，而不是总行的数，只是想用我的结果来数一组又一组的数

course       lecturer
comp1111     Jim
comp1100     Jim
comp1100     Jim
infs2321     Jess
infs2321     Jess
econ1222     Helen

my result after count should be

我数过的结果应该是

lecturer    count
Jim          3
Jess         2
Helen        1

3 个解决方案

#1

I don't see why you want a group by after you have grouped. You get your desired result by doing just one group. Please have a look at this sqlfiddle to see it working live.

我不明白为什么你在分组后还要分组。只做一组就能得到想要的结果。请查看这个sqlfiddle，看看它是如何工作的。

CREATE TABLE Table1
    (`course` varchar(8), `lecturer` varchar(5))
;

INSERT INTO Table1
    (`course`, `lecturer`)
VALUES
    ('comp1111', 'Jim'),
    ('comp1100', 'Jim'),
    ('comp1100', 'Jim'),
    ('infs2321', 'Jess'),
    ('infs2321', 'Jess'),
    ('econ1222', 'Helen')
;

select
lecturer, count(*)
from
Table1
group by lecturer desc;

| LECTURER | COUNT(*) |
-----------|----------|--
|      Jim |        3 |
|     Jess |        2 |
|    Helen |        1 |

EDIT:

编辑:

You don't need an extra table. To get the row with the largest count you can simply do

你不需要额外的桌子。要得到具有最大计数的行，只需执行以下操作

select
lecturer, count(*)
from
Table1
group by lecturer
order by count(*) desc
limit 1;

for MySQL or

MySQL或

select top 1 
lecturer, count(*)
from
Table1
group by lecturer
order by count(*) desc;

for MS SQL Server. In my first answer I had GROUP BY lecturer DESC which is the same as GROUP BY lecturer ORDER BY COUNT(*) DESC because in MySQL GROUP BY implies an ORDER BY.

MS SQL Server。在我的第一个答案中，我有一个由讲师DESC组成的组，这和用COUNT(*) DESC进行分组是一样的，因为在MySQL组中，BY表示ORDER BY。

If this is not what you want, be careful with using MAX() function. When you simply do for example

如果这不是您想要的，请小心使用MAX()函数。举个例子

select
lecturer, max(whatever)
from
Table1
group by lecturer;

you don't necessarily get the row with holding the max of whatever.

你不一定能得到包含最大值的那一排。

You can also do

你也可以做

select
lecturer, max(whatever), min(whatever)
from
Table1
group by lecturer;

See? You just get the value returned by the function, not the row belonging to it. For examples how to solve this, please refer to this manual entry.

看到了吗?你只会得到函数返回的值，而不是它所属的行。有关如何解决此问题的示例，请参阅本手册条目。

I hope I didn't confuse you now, this is probably more than you wanted to know, because above is especially for groups. I think what you really want to do is simply ordering the table the way you want, then pick just one row, like mentioned above.

我希望我没有把你们弄糊涂，这可能比你们想知道的要多，因为上面是特别针对群体的。我认为你真正想做的是简单地按你想要的方式排序，然后只选择一行，就像上面提到的那样。

#2

Try this. It might work

试试这个。它可能工作

SELECT LECTURER, COUNT(*) 
FROM 
(SELECT LECTURER, COURSE
FROM TABLE
WHERE 
GROUP BY LECTURER, COURSE )
GROUP BY LECTURER;

#3

try to this command in mysql

在mysql中尝试这个命令。

============================

= = = = = = = = = = = = = = = = = = = = = = = = = = = =

select
lecturer, count(*)
from
Course_detail
group by lecturer desc;

#1