Mysql Group By get latest record with Count
1
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
mysql sql
asked Nov 16 '18 at 7:05
shreyas dharavshreyas dharav
17410
add a comment |
1
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
mysql sql
asked Nov 16 '18 at 7:05
shreyas dharavshreyas dharav
17410
Possible duplicate of Retrieving the last record in each group - MySQL
– Nick
Nov 16 '18 at 7:12
No, I wanted count as well. No answer there.
– shreyas dharav
Nov 16 '18 at 7:14
What is your expected output? Can you please paste that?
– Mayank Porwal
Nov 16 '18 at 7:15
Check the last image. But it has incorrect count values.
– shreyas dharav
Nov 16 '18 at 7:17
add a comment |
1
1
1
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
mysql sql
asked Nov 16 '18 at 7:05
shreyas dharavshreyas dharav
17410
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
mysql sql
mysql sql
asked Nov 16 '18 at 7:05
shreyas dharavshreyas dharav
17410
asked Nov 16 '18 at 7:05
shreyas dharavshreyas dharav
17410
edited Nov 16 '18 at 7:22
shreyas dharav
asked Nov 16 '18 at 7:05
shreyas dharavshreyas dharav
17410
asked Nov 16 '18 at 7:05
shreyas dharavshreyas dharav
17410
asked Nov 16 '18 at 7:05
shreyas dharavshreyas dharav
17410
17410
Possible duplicate of Retrieving the last record in each group - MySQL
– Nick
Nov 16 '18 at 7:12
No, I wanted count as well. No answer there.
– shreyas dharav
Nov 16 '18 at 7:14
What is your expected output? Can you please paste that?
– Mayank Porwal
Nov 16 '18 at 7:15
Check the last image. But it has incorrect count values.
– shreyas dharav
Nov 16 '18 at 7:17
add a comment |
Possible duplicate of Retrieving the last record in each group - MySQL
– Nick
Nov 16 '18 at 7:12
No, I wanted count as well. No answer there.
– shreyas dharav
Nov 16 '18 at 7:14
What is your expected output? Can you please paste that?
– Mayank Porwal
Nov 16 '18 at 7:15
Check the last image. But it has incorrect count values.
– shreyas dharav
Nov 16 '18 at 7:17
Possible duplicate of Retrieving the last record in each group - MySQL
– Nick
Nov 16 '18 at 7:12
Possible duplicate of Retrieving the last record in each group - MySQL
– Nick
Nov 16 '18 at 7:12
No, I wanted count as well. No answer there.
– shreyas dharav
Nov 16 '18 at 7:14
No, I wanted count as well. No answer there.
– shreyas dharav
Nov 16 '18 at 7:14
What is your expected output? Can you please paste that?
– Mayank Porwal
Nov 16 '18 at 7:15
What is your expected output? Can you please paste that?
– Mayank Porwal
Nov 16 '18 at 7:15
Check the last image. But it has incorrect count values.
– shreyas dharav
Nov 16 '18 at 7:17
Check the last image. But it has incorrect count values.
– shreyas dharav
Nov 16 '18 at 7:17
add a comment |
2 Answers
2
active
oldest
votes
2
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;
answered Nov 16 '18 at 7:08
Tim BiegeleisenTim Biegeleisen
234k13100158
add a comment |
-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;
answered Nov 16 '18 at 7:46
Atul AkabariAtul Akabari
954
3
This is a (broken) copy of my answer. Plagiarism = you get downvoted.
– Tim Biegeleisen
Nov 16 '18 at 8:07
If you need to add a new answer, please also add an explanation to the code
– Nico Haase
Nov 16 '18 at 9:47
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53332985%2fmysql-group-by-get-latest-record-with-count%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
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;
answered Nov 16 '18 at 7:08
Tim BiegeleisenTim Biegeleisen
234k13100158
add a comment |
2
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;
answered Nov 16 '18 at 7:08
Tim BiegeleisenTim Biegeleisen
234k13100158
add a comment |
2
2
2
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;
answered Nov 16 '18 at 7:08
Tim BiegeleisenTim Biegeleisen
234k13100158
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;
answered Nov 16 '18 at 7:08
Tim BiegeleisenTim Biegeleisen
234k13100158
answered Nov 16 '18 at 7:08
Tim BiegeleisenTim Biegeleisen
234k13100158
answered Nov 16 '18 at 7:08
Tim BiegeleisenTim Biegeleisen
234k13100158
answered Nov 16 '18 at 7:08
Tim BiegeleisenTim Biegeleisen
234k13100158
234k13100158
add a comment |
add a comment |
-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;
answered Nov 16 '18 at 7:46
Atul AkabariAtul Akabari
954
3
This is a (broken) copy of my answer. Plagiarism = you get downvoted.
– Tim Biegeleisen
Nov 16 '18 at 8:07
If you need to add a new answer, please also add an explanation to the code
– Nico Haase
Nov 16 '18 at 9:47
add a comment |
-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;
answered Nov 16 '18 at 7:46
Atul AkabariAtul Akabari
954
3
This is a (broken) copy of my answer. Plagiarism = you get downvoted.
– Tim Biegeleisen
Nov 16 '18 at 8:07
If you need to add a new answer, please also add an explanation to the code
– Nico Haase
Nov 16 '18 at 9:47
add a comment |
-1
-1
-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;
answered Nov 16 '18 at 7:46
Atul AkabariAtul Akabari
954
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;
answered Nov 16 '18 at 7:46
Atul AkabariAtul Akabari
954
edited Nov 16 '18 at 10:23
answered Nov 16 '18 at 7:46
Atul AkabariAtul Akabari
954
answered Nov 16 '18 at 7:46
Atul AkabariAtul Akabari
954
answered Nov 16 '18 at 7:46
Atul AkabariAtul Akabari
954
954
3
This is a (broken) copy of my answer. Plagiarism = you get downvoted.
– Tim Biegeleisen
Nov 16 '18 at 8:07
If you need to add a new answer, please also add an explanation to the code
– Nico Haase
Nov 16 '18 at 9:47
add a comment |
3
This is a (broken) copy of my answer. Plagiarism = you get downvoted.
– Tim Biegeleisen
Nov 16 '18 at 8:07
If you need to add a new answer, please also add an explanation to the code
– Nico Haase
Nov 16 '18 at 9:47
3
3
This is a (broken) copy of my answer. Plagiarism = you get downvoted.
– Tim Biegeleisen
Nov 16 '18 at 8:07
This is a (broken) copy of my answer. Plagiarism = you get downvoted.
– Tim Biegeleisen
Nov 16 '18 at 8:07
If you need to add a new answer, please also add an explanation to the code
– Nico Haase
Nov 16 '18 at 9:47
If you need to add a new answer, please also add an explanation to the code
– Nico Haase
Nov 16 '18 at 9:47
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53332985%2fmysql-group-by-get-latest-record-with-count%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Possible duplicate of Retrieving the last record in each group - MySQL
– Nick
Nov 16 '18 at 7:12
No, I wanted count as well. No answer there.
– shreyas dharav
Nov 16 '18 at 7:14
What is your expected output? Can you please paste that?
– Mayank Porwal
Nov 16 '18 at 7:15
Check the last image. But it has incorrect count values.
– shreyas dharav
Nov 16 '18 at 7:17