Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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;
share|improve this answer
    
It's not the answer, but it got me to the answer. Apparently, each computer is listed under every division. ARGH! –  Jacobm001 Aug 6 '13 at 16:41
    
Thank you for accepting. I glad my random brianstorm lead you to your needed answer :-) –  RolandoMySQLDBA Aug 6 '13 at 16:43
1  
I suggest you also change the R.usageWhen LIKE "2012-07-08T12:%" to (R.usageWhen >= '2012-07-08T12:00:00' AND R.usageWhen < '2012-07-08T13:00:00') so index use is possible. –  ypercube Aug 6 '13 at 16:45
    
@RolandoMySQLDBA: No problem, thanks for a shot in the dark. I often hesitate to do on other sites because people love their down-votes! Can I actually use the greater/less than statements here? The data is unfortunately not stored as a date, but rather as an indexed varchar. –  Jacobm001 Aug 6 '13 at 16:54
1  
Even if they are strings, as long as the format of the timestamp is maintained as YYY-MM-DDTHH:MM:SS, and the column is indexed as @ypercube hinted at, you should be fine. –  RolandoMySQLDBA Aug 6 '13 at 16:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.