Mysql Group By get latest record with Count

Question

This is my customer table.

I want to group by emp_id alongwith the count. But Group By gets the 'first' record and not the 'newest' one.

I have tried various queries, like this

SELECT id, emp_id, COUNT( * ) AS count, created_at
FROM  customer c
WHERE  created_at = (
SELECT MAX(  created_at ) 
FROM  customer c2
WHERE c2.emp_id = c.emp_id
)
GROUP BY  emp_id 
ORDER BY  created_at DESC 
LIMIT 0 , 30

But cannot get the count. Please help.

Edit: this answer doesn't help to obtain count

Possible duplicate of [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — Nick, Nov 16 '18 at 07:12

score 2 · Accepted Answer · answered Nov 16 '18 at 07:08

Try joining to a subquery:

SELECT c1.id, c1.emp_id, c1.created_at, c2.cnt
FROM customer c1
INNER JOIN
(
    SELECT emp_id, MAX(created_at) AS max_created_at, COUNT(*) AS cnt
    FROM customer
    GROUP BY emp_id
) c2
    ON c1.emp_id = c2.emp_id AND c1.created_at = c2.max_created_at;

Atul Akabari · Answer 2 · 2018-11-16T10:23:37.383

-1

please try this 
SELECT cust1.id, cust1.emp_id, cust1.created_at, cust2.cnt
FROM customer cust1
INNER JOIN
(
    SELECT emp_id, MAX(created_at) AS max_created_at, COUNT(*) AS count
    FROM customer
    GROUP BY emp_id
) cust2
    ON cust1.emp_id = cust2.emp_id AND cust1.created_at = cust2.max_created_at;

edited Nov 16 '18 at 10:23

answered Nov 16 '18 at 07:46

Atul Akabari

93
4

3

This is a (broken) copy of my answer. Plagiarism = you get downvoted. – Tim Biegeleisen Nov 16 '18 at 08:07
If you need to add a new answer, please also add an explanation to the code – Nico Haase Nov 16 '18 at 09:47

Mysql Group By get latest record with Count

2 Answers2