CODEWITHSUNDEEP
sqldatabasecountgroup-by
Cherry Peng
Cherry Peng

Reputation: 13

IN SQL count after group by

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

Upvotes: 1

Views: 1375

Answers (3)

fancyPants
fancyPants

Reputation: 51888

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.

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 

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.

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

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.

Upvotes: 3

Dev
Dev

Reputation: 3580

try to this command in mysql

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

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

Upvotes: 0

Abhijith Nagarajan
Abhijith Nagarajan

Reputation: 4030

Try this. It might work

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

Upvotes: 0

Related Questions