Select months even without records

Question

I need to select the data stored in the database and separate them by month, but when the month does not have data yet, the month is not returned.

I need the month to be returned with the value reset but bring the month anyway.

My Query this way:

SELECT 
  (year(article.content_path)) as anoAtual,
  MONTHNAME(STR_TO_DATE(month(article.content_path), '%m')) AS MES,
  TIME_FORMAT(SEC_TO_TIME(Sum(time_accounting.time_unit)*60),"%H:%i") as tempo_total,
  concat(users.first_name, ' ',customer_user.last_name) as usuario,
  customer_user.email
FROM 
  article,
  time_accounting,
  users,
  customer_user
where 
  article.id = time_accounting.article_id and
  article.create_by = users.id and
  EXTRACT(YEAR FROM article.content_path) IN (2018) and 
  users.first_name = customer_user.first_name and 
  users.last_name like concat(customer_user.last_name,'%') and
  customer_user.customer_id = empresa and
  users.valid_id = 1 and
  customer_user.email in (emails)
group by 
  article.create_by,
  year(article.content_path),
  MES
order by 
  article.create_by,
  year(article.content_path),
  month(article.content_path)

I get the following return:

In this case, I need the month of December to come, even though I still have no records this month. I tried something similar with what I need with a Query simpler:

SELECT
month(article.change_time) as  mes,
SEC_TO_TIME(SUM(IFNULL(TIME_TO_SEC(STR_TO_DATE(TIMEDIFF(
(SELECT value_date FROM otrs.dynamic_field_value where object_id in (t2.object_id) and field_id = 79),
(SELECT value_date FROM otrs.dynamic_field_value where object_id in (t2.object_id) and field_id = 78)),'%H:%i:%s')),0))) as tempoDeslocamento
 FROM otrs.dynamic_field_value t2, otrs.article 
 where otrs.article.id = t2.object_id and year(article.change_time) = 2018 and article.create_by = 9
and field_id in (78)
group by month(article.change_time);

But even so it only returns the records in the months that have records.

Missed the month 9 and the month 12 however the IFNULL should not treat this?

Answer 1

Try it this way:

SELECT      YEAR(IFNULL(A.content_path, NOW()))                                                     AS anoAtual
        ,   MONTHNAME(M.Mes)                                                        AS Mes
        ,   TIME_FORMAT(SEC_TO_TIME(SUM(IFNULL(TA.time_unit, 0)) * 60), "%H:%i")    AS tempo_total
        ,   CONCAT(U.first_name, ' ', CU.last_name)                                 AS usuario
        ,   CU.email
FROM        (
                SELECT          1   AS Mes
                UNION SELECT    2   AS Mes
                UNION SELECT    3   AS Mes
                UNION SELECT    4   AS Mes
                UNION SELECT    5   AS Mes
                UNION SELECT    6   AS Mes
                UNION SELECT    7   AS Mes
                UNION SELECT    8   AS Mes
                UNION SELECT    9   AS Mes
                UNION SELECT    10  AS Mes
                UNION SELECT    11  AS Mes
                UNION SELECT    12  AS Mes
            ) AS M
LEFT JOIN   article         A   ON  MONTH(A.content_path) = M.Mes
LEFT JOIN   time_accounting TA  ON  TA.article_id               = A.id
LEFT JOIN   users           U   ON  U.id                        = A.create_by
LEFT JOIN   customer_user   CU  ON  CU.first_name               = U.first_name
                                AND CU.customer_id              = empresa
WHERE       EXTRACT(YEAR FROM A.content_path)   IN (2018)
        and U.last_name                         LIKE CONCAT(CU.last_name, '%')
        and U.valid_id                          = 1 
        and CU.email                            IN (emails)
GROUP BY    A.create_by
        ,   YEAR(A.content_path)
        ,   M.Mes
ORDER BY    A.create_by
        ,   YEAR(A.content_path)
        ,   M.Mes

The idea is to make a JOIN with a table already with every month and then present the information of each one.

Was placed the IFNULL if one of the months does not have information, the value "0" is shown in the column tempo_total.

Answer 2

You can use all your query in a LEFT JOIN:

SELECT MONTHNAME(m.id) AS mes, x.*
  FROM (
    SELECT 1 AS id
    UNION SELECT 2 AS id
    UNION SELECT 3 AS id
    UNION SELECT 4 AS id
    UNION SELECT 5 AS id
    UNION SELECT 6 AS id
    UNION SELECT 7 AS id
    UNION SELECT 8 AS id
    UNION SELECT 9 AS id
    UNION SELECT 10 AS id
    UNION SELECT 11 AS id
    UNION SELECT 12 AS id
  ) AS m
      LEFT JOIN (
        SELECT (YEAR(a.content_path)) as anoAtual,
               MONTHNAME(STR_TO_DATE(MONTH(article.content_path), '%m')) AS MES,
               TIME_FORMAT(SEC_TO_TIME(SUM(ta.time_unit) * 60), "%H:%i") as tempo_total,
               CONCAT(u.first_name, ' ', cu.last_name) as usuario,
               cu.email
          FROM article a
               INNER JOIN time_accounting ta ON a.id = ta.article_id
               INNER JOIN users u ON a.create_by = u.id
               INNER JOIN customer_user cu ON u.first_name = cu.first_name
         WHERE EXTRACT(YEAR FROM a.content_path) IN (2018)
           AND u.last_name LIKE concat(cu.last_name,'%')
           AND cu.customer_id = empresa
           AND u.valid_id = 1
           AND cu.email IN (emails)
         GROUP BY a.create_by,
                  YEAR(a.content_path),
                  MES
         ORDER BY a.create_by,
                  YEAR(a.content_path),
                  MONTH(a.content_path)
      ) x ON MONTHNAME(m.id) = x.mes

Answer 3

Whereas values will always be returned to the 12 months of the year, the most practical way I see would be to create a temporary table with the values already consulted, adding a line with time value 0 for each month zeroed. Then it would only be to consult the sum on top of this table; what has value, would add two lines (zero plus the total value), which would not return only the 0 (to make it easier, add a column of numerical month):

--variável para utilização do ano em mais de um ponto
set @anoConsulta = 2018;

--criação da tabela com os dados que você já tem {mesmo select adicionando a coluna criador que é usada na ordenação, e MES_NUMERICO para facilitar o filtro}

CREATE TEMPORARY TABLE IF NOT EXISTS tmpTab AS (
    SELECT 
      (year(article.content_path)) as anoAtual,
      MONTHNAME(STR_TO_DATE(month(article.content_path), '%m')) AS MES,
      month(article.content_path) AS MES_NUMERICO,
      TIME_FORMAT(SEC_TO_TIME(Sum(time_accounting.time_unit)*60),"%H:%i") as tempo_total,
      concat(users.first_name, ' ',customer_user.last_name) as usuario,
      customer_user.email as email,
      article.create_by as criador
    FROM 
      article,
      time_accounting,
      users,
      customer_user
    where 
      article.id = time_accounting.article_id and
      article.create_by = users.id and
      EXTRACT(YEAR FROM article.content_path) IN (@anoConsulta) and 
      users.first_name = customer_user.first_name and 
      users.last_name like concat(customer_user.last_name,'%') and
      customer_user.customer_id = empresa and
      users.valid_id = 1 and
      customer_user.email in (emails)
    group by 
      article.create_by,
      year(article.content_path),
      MES);

-- adicionar à tabela os meses zerados
DECLARE contador INT DEFAULT 1;

WHILE (contador <= 12) DO
  IF NOT EXISTS (SELECT 1 FROM tmpTab WHERE MES_NUMERICO = contador) THEN
    INSERT INTO tmpTab (anoAtual, MES, MES_NUMERICO, tempo_total, usuario, email, criador)
    VALUES (@anoConsulta, contador, MONTHNAME(concat(@anoConsulta','-',contador,'-01'), 0, 'nome qualquer', '[email protected]');
  END IF;

  SET contador = contador + 1;
END WHILE;

--agora todos os meses estão na tabela temporária, basta retorná-los
SELECT anoAtual, MES, tempo_total, usuario, email
FROM tmpTab
ORDER BY criador, anoAtual, MES;