Mysql max of counts per group

sql max count per group
count(max()) sql
mysql max count
mysql having max(count)
mysql count max occurrences
mysql max date
max row count in sql
sql max count mysql

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 |
+------+---------+-----------+

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

select max() from count(), down vote accepted. That just means MySQL insists that you give the inner SELECT a name, like: SELECT MAX(counted) FROM ( SELECT COUNT(*) AS counted FROM table_actions WHERE status = "good" GROUP BY user ) AS counts;. That just means MySQL insists that you give the inner SELECT a name, like: SELECT MAX(counted) FROM ( SELECT COUNT(*) AS counted FROM table_actions WHERE status = "good" GROUP BY user ) AS counts;

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

SQL MAX count() and having, SELECT MAX (mycount) FROM (SELECT agent_code,COUNT(agent_code) mycount FROM orders GROUP BY agent_code);. Sample table:  MySQL MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma-separated columns).

Try this!

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

:)

Can I use MAX(COUNT()) in SQL? - Quynh Nhu To Tuan, do we limit these results for each year to the highest occupation count? We'd expect to use MAX on COUNT(occupation) and add the year to the GROUP BY. MySQL COUNT() function with group by on multiple columns The following MySQL statement returns number of publishers in each city for a country. Grouping operation is performed on country and pub_city column with the use of GROUP BY and then COUNT() counts the number of publishers for each groups.

Maximum of Column per Group, 3.6.3 Maximum of Column per Group. Task: Find the highest price per article. SELECT article, MAX(price) AS price FROM shop GROUP BY article ORDER BY​  SQL COUNT ( ) with group by and order by . In this page, we are going to discuss the usage of GROUP BY and ORDER BY along with the SQL COUNT() function. The GROUP BY makes the result set in summary rows by the value of one or more columns. Each same value on the specific column will be treated as an individual group.

MySQL Tutorial :: 7.4 The Rows Holding the Group-wise , Task: For each article, find the dealer or dealers with the most expensive price. shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY  This is a slightly harder problem to solve. Finding a single row from each group is easy with SQL’s aggregate functions (MIN(), MAX(), and so on). Finding the first several from each group is not possible with that method because aggregate functions only return a single value. Still, it’s possible to do.

MySQL MAX Function, The MySQL MAX() function returns the maximum value in a set of values. Notice that DISTINCT has effects in other aggregate functions such as COUNT() , SUM() , and AVG() . D) Using MySQL MAX() with GROUP BY clause example. SELECT productVendor, COUNT (*) FROM products GROUP BY productVendor HAVING COUNT (*) >= 9 ORDER BY COUNT (*) DESC; C) MySQL COUNT IF example You can use a control flow expression and functions e.g., IF , IFNULL , and CASE in the COUNT() function to count rows whose values match a condition.

Comments
  • Do you have a primary key on the table ?
  • Yeah there is a primary key, "id_test"
  • Actually you do not need the primary key, added an answer check out.
  • Maybe not the best way, but truth is you are the only one with a good answer.
  • Sorry I forgot to add how should looks the solution.