I have a table called activity
that uses the following columns:
LoginDate, LoginID, Department, IsMGR
I tried this query:
SELECT COUNT(*),
DATE_FORMAT( LoginDate, '%m%d%Y' ) AS LogDate,
LoginID, Department
FROM `activity`
WHERE IsMGR = '1'
AND LoginDate > '2011-01-01 00:00:00'
GROUP BY LoginID, LogDate
ORDER BY LoginID,LogDate DESC
What I end up with is:
3 02172011 sdavis accounting 1 02162011 开发者_如何学运维sdavis accounting 2 02132011 sdavis accounting 1 02102011 sdavis accounting 5 02092011 sdavis accounting 4 02082011 sdavis accounting 1 03182011 dmorris marketing 1 03092011 dmorris marketing 6 04142011 cchan technology 1 03282011 cchan technology 2 03262011 cchan technology 2 03112011 cchan technology 1 03102011 cchan technology
Which is giving a count of how many times on each of those days that they logged in. What I'd want is a only one count per day and having all of the days grouped for each person with a count of how many days they logged in, along with the last day. Using the data sample above, the output I desire should be
6 02172011 sdavis accounting 2 03182011 dmorris marketing 5 04142011 cchan technology
So what I'd want is to somehow group the above grouped results by a 2nd set of criteria...?
Try this:
SELECT COUNT(distinct LoginDate) AS NumLogins,
DATE_FORMAT( MAX(LoginDate), '%m%d%Y' ) AS LastLogDate,
LoginID, Department
FROM activity
WHERE IsMGR='1' AND LoginDate > '2011-01-01 00:00:00'
GROUP BY LoginID, Department
ORDER BY LoginID, Department DESC
select
count(distinct LoginDate) as loginCount,
max(LoginDate) as lastLoginDate,
LoginID,
Department
FROM `activity`
where IsMGR='1'
and LoginDate > '2011-01-01 00:00:00'
GROUP BY LoginID, Department
ORDER BY LoginID, lastLoginDate DESC
精彩评论