CODEWITHSUNDEEP
mysqlsqlgroup-by
user4035
user4035

Reputation: 23729

MySQL get MAX value with COUNT for every row in GROUP BY

Suppose, I have a table

           pages_urls
+----+---------+---------------+
| id | site_id | download_date |
+----+---------+---------------+
|  1 |       1 | 2012-01-01    |
|  2 |       1 | 2012-12-31    |
|  3 |       2 | 2012-01-01    |
|  4 |       2 | 2012-12-31    |
+----+---------+---------------+

For every site_id I want to select:

  1. The last download date
  2. Number of records in the table for this site

I tried this query:

SELECT
    pages_urls.site_id,
    max_table.download_date,
    COUNT(*)
FROM
    pages_urls
LEFT JOIN
(
SELECT
    site_id,
    MAX(download_date) AS download_date
FROM
    pages_urls AS max_pages_urls
WHERE
    max_pages_urls.site_id=site_id
) AS max_table
ON
    pages_urls.site_id=max_table.site_id
GROUP BY
    site_id;

But I got this instead of the desired result:

            desired result           ║            my query result         
+---------+---------------+----------║---------+---------------+----------+
| site_id | download_date | COUNT(*) ║ site_id | download_date | COUNT(*) |
+---------+---------------+----------║---------+---------------+----------+
|       1 | 2012-12-31    |        2 ║       1 | 2012-12-31    |        2 |
|       2 | 2012-12-31    |        2 ║       2 | NULL          |        2 |
+---------+---------------+----------║---------+---------------+----------+
                                     ║

fiddle with table and query

How can get the necessary information?

Upvotes: 0

Views: 2538

Answers (1)

juergen d
juergen d

Reputation: 204766

SELECT site_id, MAX(download_date) AS download_date, count(*)
FROM pages_urls
GROUP BY site_id;

SQLFiddle demo

Upvotes: 3

Related Questions