0

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!

  • Search for ROLLUP – ypercubeᵀᴹ Feb 21 '18 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 '18 at 10:22
  • Just add WITH ROLLUP, after the group by? Did you check MySQL docs? – ypercubeᵀᴹ Feb 21 '18 at 10:24
  • @ypercubeᵀᴹ - I did but the output is incorrect like this pastebin.com/XJJDhJS9 – james Feb 21 '18 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? – Lennart Feb 21 '18 at 12:18
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.

  • 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 '18 at 12:51
  • Sorry about that, copy paste error. I'll update the answer – Lennart Feb 21 '18 at 12:54
  • You're welcome, out of curiosity which interpretation of the problem was correct? – Lennart Feb 21 '18 at 13:13
  • 49 is meaningless. – Rick James Feb 26 '18 at 3:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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