1

I have the following mysql query.

SELECT 
COUNT(DISTINCT l.userid) AS 'Logins', 
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS 'Month'
FROM databasename.tablename l
WHERE l.action = 'loggedin' AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018' 
GROUP BY MONTH(FROM_UNIXTIME(l.timecreated))

It will produce the output of:

Logins Month --------- ------- 33 January 16 February

How does the mysql query looks like to get the sum of column Logins like 49 on this example?

Thanks!

7
  • Search for ROLLUP Feb 21, 2018 at 9:59
  • Thanks ypercubeᵀᴹ I'm not good in mysql so I am a bit confuse how to use sum on my query with rollup.
    – james
    Feb 21, 2018 at 10:22
  • Just add WITH ROLLUP, after the group by? Did you check MySQL docs? Feb 21, 2018 at 10:24
  • @ypercubeᵀᴹ - I did but the output is incorrect like this pastebin.com/XJJDhJS9
    – james
    Feb 21, 2018 at 10:37
  • Why are you using different expressions in the select clause and in the group by clause? Second, how do you know that the result should be 49? Are you sure no user logged in in both Jan and Feb? Feb 21, 2018 at 12:18

1 Answer 1

0

I would start with using the same expression in both select and group by:

SELECT COUNT(DISTINCT l.userid) AS Logins 
     , DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS Month
FROM databasename.tablename l
WHERE l.action = 'loggedin' 
  AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018' 
GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M');

If you want to count the total number of distinct logins you can add ROLLUP as ypercubeᵀᴹ suggests. You can use COALESCE to get a label for the total:

SELECT COUNT(DISTINCT l.userid) AS Logins 
     , COALESCE(DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M'), 'TOTAL') AS Month
FROM databasename.tablename l
WHERE l.action = 'loggedin' 
  AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018' 
GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') WITH ROLLUP;

In general, the total is less than the sum of the parts. It will be the same only if the logins from Jan is disjoint with the logins from Feb.

If you instead want to sum the number of distinct logins you can do it like:

SELECT SUM(logins), COALESCE(Month, 'Summary') as Month
FROM (
    SELECT COUNT(DISTINCT l.userid) AS Logins 
         , DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS Month
    FROM databasename.tablename l
    WHERE l.action = 'loggedin' 
      AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018' 
    GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M')
) AS T
GROUP BY Month WITH ROLLUP;

This will result in the sum of the number of distinct logins for each month, which will be 49 in your case.

4
  • Thanks @Lennart. However, I got error message of Error Code: 1582. Incorrect parameter count in the call to native function 'DATE_FORMAT'...
    – james
    Feb 21, 2018 at 12:51
  • Sorry about that, copy paste error. I'll update the answer Feb 21, 2018 at 12:54
  • You're welcome, out of curiosity which interpretation of the problem was correct? Feb 21, 2018 at 13:13
  • 49 is meaningless.
    – Rick James
    Feb 26, 2018 at 3:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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