mysql - Date column with associated MAX count value - Stack Overflow

admin管理员组

文章数量:1335825

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

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

• mysql

Share Improve this question Follow asked Nov 19, 2024 at 21:03 MarkSMarkS 1,53955 gold badges2323 silver badges4545 bronze badges 3

• Why do you think you need AS X after GROUP BY? Just get rid of that. – Barmar Commented Nov 19, 2024 at 21:18
• Because it generates a specific error telling me I am required to assign an alias."Every derived table must have its own alias" – MarkS Commented Nov 19, 2024 at 21:19
• But you didn't give the alias to a table. Put it after the end of the subquery. – Barmar Commented Nov 19, 2024 at 21:20

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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
);

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
);

本文标签： mysqlDate column with associated MAX count valueStack Overflow