MySQL COUNT, SUM with GROUP BY showing nonexistent values?

Question

MySQL

There is a table `table` of calls with user, datetime, duration, call_id
fields. Data is duplicated in user, datetime fields.
I get the total number of calls and their duration for the period as follows:

SELECT `user`, DATE(`datetime`) as `date`, count(`duration`) as `count` 
FROM `table`
WHERE
  `datetime` >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY `date` DESC, `user` ASC

How can I get
the missing unique value of the `user` field (SELECT DISTINCT `user` FROM `table`) in the output table
for days where there were no calls for these "user"?

Answer 1

Fill in user variables min. and max. datetime field value:

SELECT MIN(`datetime`), MAX(`datetime`) 
FROM `table`
WHERE TIMESTAMP BETWEEN "2014-10-01" AND "2017-10-31"
INTO @min_d, @max_d;
SET @n:=date(@max_d + INTERVAL 1 DAY);

All dates of requested days:

SELECT * FROM

  /* Все пользователи */
  (SELECT DISTINCT `user`
  FROM `table` 
  WHERE 
  NOT ISNULL(`user`)
  ) AS `users`

  RIGHT OUTER JOIN

  /* Все даты запрошенных дней */
  (SELECT (SELECT @n:= @n - INTERVAL 1 DAY) AS `date`
  FROM `table`
  WHERE @n >= DATE_SUB(@max_d, INTERVAL DATEDIFF(@max_d,@min_d) DAY) AND 
  NOT ISNULL(`user`)
  ) AS `datetime`

ON TRUE