Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Date column with associated MAX count value

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
Tags:

2

Answers


  1. 0

    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
);
    Login or Signup to reply.
  2. 0

    The alias should be on the subquery, not after GROUP BY.

    SELECT
 DATE(rental_date) AS `Rental date`,
 COUNT(*)
FROM rental
GROUP BY `Rental date`
HAVING COUNT(*) = (
    SELECT MAX(Count)
    FROM (
          SELECT
           DATE(rental_date) AS `Rental date`,
           COUNT(*) AS Count
          FROM rental
          GROUP BY `Rental date`
    ) AS X
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search