MySQL Grouping Error

Question

I'm trying to run a query on a table to see how many unique users have a usage record in the system at a given point. I've been working with the following query, but I've yet to see a proper result.

SELECT count(distinct usageUser), divisionName
FROM records R 
INNER JOIN locate L  
    ON L.computerID=R.usageComputerID
WHERE R.usageWhen LIKE "2012-07-08T12:%"
GROUP BY L.divisionName;

Currently the query returns 18, for each division in the joined table. Without the GROUP BY clause I get the same number of records.

EDIT: I ran the query again, with suggestions from a comment. By removing the group by and count clause, I get this this (too big to post). This data is very poorly formatted, unfortunately it's inherited and fairly large.

It is not possible for these users to have used every lab like it's listed.

This was originally posted to server fault, and was migrated to StackOverFlow. I was then pointed to here saying this might actually be the most appropriate place for it.

Answer 1

Perhaps you could look for the number of records for each usageUser as well

This query will give the record count for all users per division

SELECT count(1) usageUser_Records,L.divisionName,R.usageUser 
FROM records R 
INNER JOIN locate L  
    ON L.computerID=R.usageComputerID
WHERE R.usageWhen LIKE "2012-07-08T12:%"
GROUP BY L.divisionName,R.usageUser;

This query will give the record count for all divisions per user (reversed GROUP BY)

SELECT count(1) usageUser_Records,L.divisionName,R.usageUser 
FROM records R 
INNER JOIN locate L  
    ON L.computerID=R.usageComputerID
WHERE R.usageWhen LIKE "2012-07-08T12:%"
GROUP BY R.usageUser,L.divisionName;

Looking back at your original query, if removing the count(distinct usageUser) gave you the same number of rows, my guess would be that 2012-07-08 has either one division or one user being retrieved. Looking back at your original post, you said you inherited this data. Chances are, there is only one division or one user throughout the data.

This may not be a full answer but just two suggested queries to get you to think. I hope this helps...

UPDATE 2013-08-06 12:48 EST

As suggested by @yercube

SELECT count(1) usageUser_Records,L.divisionName,R.usageUser 
FROM records R 
INNER JOIN locate L  
    ON L.computerID=R.usageComputerID
WHERE (R.usageWhen >= '2012-07-08T12:00:00' AND R.usageWhen < '2012-07-08T13:00:00')
GROUP BY L.divisionName,R.usageUser;

This query will give the record count for all divisions per user (reversed GROUP BY)

SELECT count(1) usageUser_Records,L.divisionName,R.usageUser 
FROM records R 
INNER JOIN locate L  
    ON L.computerID=R.usageComputerID
WHERE (R.usageWhen >= '2012-07-08T12:00:00' AND R.usageWhen < '2012-07-08T13:00:00')
GROUP BY R.usageUser,L.divisionName;