CODEWITHSUNDEEP
mysqlcountmax
Adrián Granado Blanco
Adrián Granado Blanco

Reputation: 27

Mysql max of counts per group

I have the following table:

+---------+--------+-------+
| client  | session| boat  |
+---------+--------+-------+
|       1 |   1943 |     3 |
|       1 |   1943 |     4 |
|       1 |   1944 |     3 |
|       1 |   1944 |     4 |
|       1 |   1945 |     4 |
|       2 |   1944 |     3 |
|       2 |   1944 |     4 |
|       3 |   1944 |     3 |
+---------+--------+-------+

And i want to get the max count of boats, then I did this:

SELECT boat,session,max(counter) as max_boats FROM (SELECT boat,COUNT(boat) as counter,session FROM `test` GROUP BY boat,session) as cont2 GROUP BY boat

Result:

+------+---------+-----------+
| boat | session | max_boats |
+------+---------+-----------+
|    3 |    1943 |         3 |
|    4 |    1943 |         2 |
+------+---------+-----------+

So, my problem is this query is returning me "session = 1943" and this is wrong. As you can see in the original table, boat 3 has three rows BUT with session 1944 and boat 4 has two sessions BUT with session 1944 too.

Problem is, when I do max(counter), MySQL returns me the first session, not the row corresponding to max(counter).

Maybe my approach is wrong and entire query is wrong.

Thanks for your answers.

Solution would be:

+------+---------+-----------+
| boat | session | max_boats |
+------+---------+-----------+
|    3 |    1944 |         3 |
|    4 |    1944 |         2 |
+------+---------+-----------+

Upvotes: 1

Views: 72

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Here is one way to get this, which may not be the best way, you may check out other options too.

select 
x.boat, 
x.session, 
max_boats
from (
  select 
  boat, 
  session, 
  count(*) cnt
  from boats
  group by boat, session
)x
join(
  select boat, 
  max(cnt) max_boats
  from(
    select 
    boat, 
    session, 
    count(*) cnt
    from boats
    group by boat, session
  )y
  group by boat
)t on x.boat = t.boat and x.cnt = t.max_boats

Upvotes: 2

Enrique Muñoz Rodas
Enrique Muñoz Rodas

Reputation: 461

Try this!

SELECT COUNT(*), client, session
FROM `test` 
GROUP BY client, session
ORDER BY COUNT(*) DESC
LIMIT 1

:)

Upvotes: 0

Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167172

Does this work?

SELECT COUNT(*) AS `max_boats`, `session` FROM `boats` GROUP BY `session` ORDER BY COUNT(*) DESC

Output:

+-----------+---------+
| max_boats | session |
+-----------+---------+
|         5 |    1944 |
|         2 |    1943 |
|         1 |    1945 |
+-----------+---------+

You can limit the output to the first one by using LIMIT 1

SELECT COUNT(*) AS `max_boats`, `session` FROM `boats` GROUP BY `session` ORDER BY COUNT(*) DESC

Output:

+-----------+---------+
| max_boats | session |
+-----------+---------+
|         5 |    1944 |
+-----------+---------+

Fiddle: http://sqlfiddle.com/#!9/59e53/3

Upvotes: 0

Related Questions