count query
-
Hello, I have table1 with fields id, date, usernamename. I can get the count of records created by user(s) for certaing date range, but if user 'A' has not inserted any records it is missing from result. What I want to achieve is to be able to display the username, with count=0. I know it should be some sort of self join but I can't manage to get it work. All suggestions appreciated!
-
Hello, I have table1 with fields id, date, usernamename. I can get the count of records created by user(s) for certaing date range, but if user 'A' has not inserted any records it is missing from result. What I want to achieve is to be able to display the username, with count=0. I know it should be some sort of self join but I can't manage to get it work. All suggestions appreciated!
You can use
GROUP BYwith theHAVINGclause.SELECT username
FROM your_table
GROUP BY username
HAVING COUNT(*) = 0 -
You can use
GROUP BYwith theHAVINGclause.SELECT username
FROM your_table
GROUP BY username
HAVING COUNT(*) = 0 -
You're right! I've should've seen that. It can't possibly work... Will something like this work?
SELECT y1.Username
FROM your_table y1
LEFT JOIN your_table y2
ON y1.Username = y2.Username
WHERE y2.date BETWEEN start AND end
GROUP BY y1.Username
HAVING COUNT(y2.Username) = 0The table alias
y1represent all usernames andy2represent only the records within the requested range.