Date column with associated MAX count value

November 19, 2024

MySQL 8.0.40

Sakila database

The following two queries correctly return two columns: Rental date and the total count for each date.

SELECT
 DATE(rental_date) AS 'Rental date',
 COUNT(DATE(rental_date)) AS 'Count for each day'
FROM rental
GROUP BY
 DATE(rental_date);

SELECT
 DATE(rental_date) AS 'Rental date',
 COUNT(*) AS 'Count'
FROM rental
GROUP BY
 DATE(rental_date);

This query correctly returns the MAX value (679).

SELECT
 MAX(Count)
FROM
 (SELECT
   COUNT(*) AS 'Count'
  FROM rental
  GROUP BY
   DATE(rental_date)) as DailyCount;

However, what I want is to return the date that has the max value along with the max value:

2005-07-31  679

This is my last attempt but it still generates errors:

SELECT
 DATE(rental_date) AS 'Rental date',
 COUNT(*)
FROM rental
HAVING COUNT(*) = (
    SELECT
     MAX(Count)
    FROM (
          SELECT
           DATE(rental_date) AS 'Rental date',
           COUNT(*) AS Count
          FROM rental
           GROUP BY
            DATE(rental_date) AS X
    )
);

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS X
    )
)' at line 14

Error position: line: 13

>Solution :

This might do the trick

SELECT
 DATE(rental_date) AS 'Rental date',
 COUNT(*) AS 'Count'
FROM rental
GROUP BY
 DATE(rental_date)
ORDER BY COUNT(*) DESC
LIMIT 1

For fetching all the rows having max count, below query can help.

SELECT
    DATE(rental_date) AS `Rental Date`,
    COUNT(*) AS `Count`
FROM rental
GROUP BY
    DATE(rental_date)
HAVING COUNT(*) = (
    SELECT MAX(`Count`) 
    FROM (
        SELECT COUNT(*) AS `Count`
        FROM rental
        GROUP BY rental_date
    ) AS m
);