CODEWITHSUNDEEP
mysqlcount
Skie
Skie

Reputation: 21

count(*) query issue

I have a table with these records:

id         code        date
-----      ------      -----
1          12          2016-01-01
2          12          2016-01-02
3          17          2016-01-03
4          12          2016-01-04
5          17          2016-01-05
6          17          2016-01-06
7          17          2016-01-07

I use this query: 

SELECT `id`, `code`, `date`, COUNT(*) AS total 
FROM `foo` 
GROUP BY (code) 
ORDER BY (id) DESC

and I get this:

id         code        date           total
-----      ------      ------         ------
3          17          2016-01-03     4
1          12          2016-01-01     3

But I need to display this:

id         code        date           total
-----      ------      ------         ------
7          17          2016-01-07     4
4          12          2016-01-04     3

Is it possible? Thank you very much.

Upvotes: 1

Views: 49

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use MAX function:

SQL Fiddle

SELECT
    MAX(id) AS id,
    code,
    MAX(date) AS date,
    COUNT(*) AS total
FROM foo
GROUP BY code
ORDER BY MAX(id) DESC;

Upvotes: 2

Related Questions