MySQL query to get sum of count distinct column value

MySQL

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!

Best Answer

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.

Related Question