Limit the count using GROUP BY

Question

I want to limit the count to 5 using COUNT(*) and group by but it returns all the rows.Consider I have a table names tbhits

         tbhits

     id    |     uname
 ------------------------
    101    |      john
    101    |      james
    101    |      henry
    101    |      paul
    101    |      jacob
    101    |      jaden
    101    |      steve
    101    |      lucas
    102    |      marie

SELECT id,COUNT(*) as 'hits' FROM tbhits GROUP BY id

returns

     id    |   hits
 --------------------
     101   |    8
     102   |    1

But I want the group by to limit maximum count to 5.

Say I have got 1000 rows I dont want to count them all, if rows are just greater than 5 then just display 5+

I tried using LIMIT 5 but it does not seem to work

SELECT id,COUNT(*) as 'hits' FROM tbhits GROUP BY id LIMIT 5 does not work.

I also used WHERE Clause

SELECT id,COUNT(*) as 'hits' FROM tbhits WHERE id = 101 GROUP BY id LIMIT 5

but it stil returns hits as 8.

          id    |   hits
        --------------------
          101   |    8

Any help is greatly appreciated.

score 5 · Accepted Answer · edited May 23 '17 at 11:59

5

LIMIT is intended to limit the number of rows you'll get from your query. I suggest you use the COUNT function as follows :

SELECT id, CASE WHEN COUNT(*) < 6 then COUNT(*) ELSE '5+' END as 'hits' 
FROM tbhits 
GROUP BY id

More details about selecting the minimum of two numbers here, and here goes the sqlfiddle (consider providing it yourself next time).

Note that I went for 6 instead of '5+' on my first suggestion, because you should not, in my opinion, mix data types. But putting 6 is not a good solution either, because someone not aware of the trick will not notice it ('5+', at least, is explicit)

As far as performance is concerned, AFAIK you should not expect MySQL to do the optimization itself.

edited May 23 '17 at 11:59

Community

1
1

answered May 14 '14 at 15:55

P. Camilleri

12,664
7
41
76

but could you tell me using MIN like you have just told is better and faster than just simple count(*)? – user3205479 May 14 '14 at 15:58
so how does the above query work , does it check for every count and when count reaches 6 it stops counting the next rows? – user3205479 May 14 '14 at 16:03
Don't rely on SQL for query optimization. – P. Camilleri May 14 '14 at 20:09

Ravi Dhoriya ツ · Answer 2 · 2014-05-14T16:09:29.303

2

LIMIT on GROUP BY clause won't actually limit the counts, it will limit the rows being outputed.

Try using if statement to compare count result,

SELECT id,if(COUNT(*)>5,'5+',COUNT(*)) as 'hits'
FROM tbhits 
GROUP BY id

O/p:

     id    |   hits
 --------------------
     101   |    5+
     102   |    1

Regarding performance issue, AFAIK GROUP BY will always lead to lead down performance and there is no direct way to limit counts in GROUP BY clause. You will have to go with either IF or CASE statement if you want solution from MySQL. Otherwise go with PHP itself.

Moreover you should have a look at GROUP BY optimization

edited May 14 '14 at 16:09

answered May 14 '14 at 15:58

Ravi Dhoriya ツ

4,435
8
37
48

Sorry but I really need to know is this faster? I think it adds extra overhead using if condition. I work using php, so a simple if condition in php could also do that? – user3205479 May 14 '14 at 16:02
GROUP BY will always lead to lead down performance and there is no direct way to limit counts in group by. You will have to go with either `IF` of `CASE` statement if you want solution from MySQL. Otherwise go with `PHP` itself. – Ravi Dhoriya ツ May 14 '14 at 16:04
I don't think the IF (or the CASE in the solution by M. Massias) is in itself a big issue for performance. However while you would hope that MySQL will manage to optimise away the COUNT(*) twice, if it doesn't that could have an impact. Possibly a slightly greater issue is the mix of data types in the result (ie char for "5+" and int for the count). – Kickstart May 14 '14 at 16:33

onedevteam.com · Answer 3 · 2014-05-14T16:06:02.280

0

Try with this?

SELECT id,COUNT(*) as 'hits' FROM tbhits GROUP BY id 
HAVING hits >= 5

edited May 14 '14 at 16:06

answered May 14 '14 at 15:58

onedevteam.com

3,838
10
41
74

Is this better and faster than just simple count(*) because we could also do using it php say if hits > 5 than 5+. so kindly let me know is this faster? – user3205479 May 14 '14 at 16:00
This is actually incorrect. This will return only rows where there is no more than 5 rows total for the id. In this case, it would *not* return the first line with 8. – ApplePie May 14 '14 at 16:05
@alexandre my mistake... i put wrong operand in query... this is fastest way to get all rows having 5 or more counts. In this case, server does everything. – onedevteam.com May 14 '14 at 16:09
Then this will not return the second row which has only 1 count :P Your answer could work if you modified your select to return 5+ and then unioned it with another query that extracts the counts lower than 5. This would be a bit longer than the other solutions provided but would work. – ApplePie May 14 '14 at 16:12
This answer turned up in the low quality review queue, presumably because you didn't explain some of the contents. If you do explain this (in your answer), you are far more likely to get more upvotes—and the questioner actually learns something! – The Guy with The Hat May 14 '14 at 16:32

score 0 · Answer 4 · answered May 14 '14 at 16:02

0

As was already said LIMIT applies last in this case, thus after the grouping. What you want to do is modify the value that is selected once the grouping is done.

This will appropriately output "5+" if you have more than 5 records for your table.

SELECT id, 
  IF(COUNT(*)>5,"5+",COUNT(*)) AS 'count'
FROM Whatever GROUP BY id

See the SQL Fiddle here: http://sqlfiddle.com/#!2/e381e/4

answered May 14 '14 at 16:02

ApplePie

8,814
5
39
60

Not sure why I'm getting -1s. My answer works as proven by the fiddle. If there is something wrong with my answer I would gladly correct it. – ApplePie May 14 '14 at 16:12

Limit the count using GROUP BY

4 Answers4