开发者

MySQL Group By & Count... Can I do a nested grouping?

开发者 https://www.devze.com 2023-03-16 20:13 出处:网络
I have a table called activity that uses the following columns: LoginDate, LoginID, Department, IsMGR I tried this query:

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
0

精彩评论

暂无评论...
验证码 换一张
取 消