Jonathon Hoaglin Jonathon Hoaglin - 5 months ago 10
MySQL Question

MySQL count with group by not returning correct result

I have a ticketing system that I am trying to run a report on. I am trying to get the number of tickets touched per user.
With this first query:

SELECT * FROM (
SELECT TicketID, UserID, EventDateTime
FROM dcscontact.ticketevents
WHERE EventDateTime BETWEEN '2016-06-22' AND '2016-06-23'
ORDER BY EventDateTime DESC) x
WHERE UserID=80
GROUP BY TicketID;


I am able to list the tickets touched for a particular user, and can count them manually:

TicketID UserID EventDateTime
99168 80 6/22/2016 13:21
99193 80 6/22/2016 7:42
99213 80 6/22/2016 13:02
99214 80 6/22/2016 6:30
99221 80 6/22/2016 6:57
99224 80 6/22/2016 7:48
99226 80 6/22/2016 6:27
99228 80 6/22/2016 8:49
99229 80 6/22/2016 8:53
99232 80 6/22/2016 9:18
99237 80 6/22/2016 13:08


But when I try to drop the WHERE UserID= statement, and try to use it as a subquery like so:

SELECT UserID, COUNT(*) as count FROM (
SELECT * FROM (
SELECT TicketID, UserID, EventDateTime
FROM dcscontact.ticketevents
WHERE EventDateTime BETWEEN '2016-06-22' AND '2016-06-23'
ORDER BY EventDateTime DESC) x
GROUP BY TicketID) y
GROUP BY UserID;


I get incorrect counts:

UserID count
9 2
28 1
31 1
42 1
80 5
95 1
99 6
108 4
116 12
117 26
123 24


As you can see, the count for UserID 80 should have been 11. most of the other results are also incorrect, they seem to all be lower numbers than I am expecting.

Am I doing something wrong with the GROUP BY/COUNT when using it on a subquery? How can I change my query to get the results I want?

Answer

Do you just want an aggregation?

SELECT UserID, COUNT(*)
FROM dcscontact.ticketevents
WHERE EventDateTime BETWEEN '2016-06-22' AND '2016-06-23'
GROUP BY UserID;

If the same ticket can appear in the data more than one time for a given user,then COUNT(DISTINCT) is more appropriate:

SELECT UserID, COUNT(DISTINCT TicketID)
FROM dcscontact.ticketevents
WHERE EventDateTime BETWEEN '2016-06-22' AND '2016-06-23'
GROUP BY UserID;