Select com agrupamento

[asimoes]

Resolvi

[asimoes]

Como tratar null? apesar de usar IFNULL não funcionou tentei também COALESCE

[asimoes]

Nada como um SELECT por cima pra resolver o mais interno

Código: Selecionar todos

SELECT X.MES, IFNULL(X.TOTAL,0) FROM (
WITH RECURSIVE Ano AS (
 SELECT 1 AS mes
 UNION
 SELECT mes + 1 AS mes
 FROM Ano
   WHERE
    Ano.mes < 12
) 
SELECT * FROM Ano
LEFT JOIN 
( SELECT D_VENC AS VENCIMENTO, Sum( apa.vl_guia) AS TOTAL, YEAR(D_VENC) AS ANO 
  FROM cobrancauti APA
  WHERE
  APA.D_PGTO IS NULL AND
  YEAR( APA.D_VENC )  = 2018
  GROUP BY 
  MONTH( APA.D_VENC )
 ) AS B ON ANO.MES = MONTH( B.VENCIMENTO )

ORDER BY MES ) X
ORDER BY X.MES

[JoséQuintas]

Usou recursive e CTE ao mesmo tempo, ou isso já tinha antes?

Tem aquela função de trazer zerado, que não lembro qual era, que acho que tem neste post.
Revi o post: COALESCE()

COALESCE( a, b, c, d, e, f, 0 )

Retorna o primeiro válido, que não seja NULL.

[asimoes]

Para um ano ok, mas para 3 anos, 2018 a 2020, USEI UM BETWEEN mas o resultado ficou errado

[asimoes]

Usou recursive e CTE ao mesmo tempo, ou isso já tinha antes?

Usei o exemplo do Alexandre, tomei uma surra pra entender mas consegui fazer funcionar, agora o problema é para um período maior por ano

[alxsts]

Olá!

Executando essa seleção dá erro

Sim. A seleção está incompleta. A definição de um CTE termina com um ou mais comandos DML (Data Management Language) ou seja, SELECT, INSERT, UPDATE ou DELETE.

Como você precisa selecionar mais de um ano e dentro de cada ano o mês se repete, no JOIN é preciso considerar o ano também...

Modifiquei o CTE para gerar os 36 meses dos anos que vi no teu código (2018,2019 e 2020). Juntei a este novo CTE o código que você postou. Como não tenho as tuas tabelas, não consegui testar. Mas acho que falta pouco. Acho que o caminho é este. Teste o código abaixo e faça as correções necessárias.

Código: Selecionar todos

WITH recursive Datas AS (
  -- "Membro Âncora"
  SELECT
    1 AS Mes,
    2018 AS Ano,
    1 as Loops
  UNION ALL
    -- "Membro Recursivo"
  SELECT
    CASE
      WHEN Loops % 12 = 0 THEN 1
      ELSE Mes + 1
    end,
    CASE
      WHEN Loops % 12 = 0 THEN Ano + 1
      ELSE Ano
    end,
    Loops + 1
  FROM Datas  /* Resultado da última iteração */
  WHERE
    Loops < 36
)
SELECT  *
  FROM (
      SELECT
        D_VENC AS VENCIMENTO,
        Coalesce(Sum(apa.vl_guia), 0) AS TOTAL,
        Coalesce(Month(D_VENC), Datas.Mes) AS MES,
        Coalesce(YEAR(D_VENC), Datas.Ano) AS ANO
      FROM cobrancauti APA
      WHERE
        APA.D_PGTO IS NULL
        AND YEAR(APA.D_VENC) BETWEEN 2018
        AND 2020
      GROUP BY
        MONTH(APA.D_VENC) )
      AS b
  LEFT JOIN ( SELECT Mes, Ano 
                FROM Datas 
            ORDER BY Ano, Mes ) AS d 
    ON d.Mes = MONTH(B.VENCIMENTO)
   AND d.Ano = Year(B.VENCIMENTO)
ORDER BY
  b.MES

O CTE eu consigo testar e está funcionando.

Recursive Common Table Expression CTE

[asimoes]

Alexandre, deu esse erro

[asimoes]

Coloquei NULL para funcionar

Coalesce(Month(D_VENC),null) AS MES,
Coalesce(YEAR(D_VENC), null) AS ANO

Mas o resultado sumarizou os 3 anos em um único ano 2018

[asimoes]

Acrescentei MONTH(apa.d_venc) ) no Group By

GROUP BY
year(APA.D_VENC), MONTH(apa.d_venc) )

Mas o resultado para os meses 4 e 9 e 10 de janeiro não sairam na lista com 0,00
Teve mês que teve pagamento ABRIL, SETEMBRO E OUTUBO e não sairam na lista com 0,00
Eles tem que aparecer mesmo com 0,00 se não o Gráfico não funciona

APA.D_PGTO IS NULL aqui que é problema, nessa query se no teve pagamento deveria aparacer com 0,00

[asimoes]

Esse aqui fez, mas para um ano, se colocar BETWEEN 2018 AND 2020 não funciona

Código: Selecionar todos

SELECT X.ANO, X.MES AS MES, IFNULL(X.TOTAL,0) AS TOTAL FROM (
WITH RECURSIVE Ano AS (
 SELECT 1 AS MES
 UNION 
 SELECT MES + 1 AS MES
 FROM ANO
   WHERE
    Ano.mes < 12
) 
SELECT * FROM ANO
LEFT JOIN 
( SELECT D_VENC AS VENCIMENTO, Sum( apa.vl_guia) AS TOTAL, YEAR(D_VENC) AS ANO 
  FROM cobrancauti APA
  WHERE
  APA.D_PGTO IS NULL AND
  YEAR( APA.D_VENC ) = 2018
  GROUP BY 
  YEAR( APA.D_VENC ), MONTH( APA.D_VENC )
 ) AS B ON ANO.MES = MONTH( B.VENCIMENTO ) 
ORDER BY MES ) X
ORDER BY  X.MES 

[alxsts]

Olá!
Ali onde você trocou Datas por NULL o certo é d.mes e d.ano.
Fica difícil sem os dados. Não consegue anexar as estruturas das tabelas com alguns dados?

[asimoes]

Inverti o LEFT JOIN agora funcionou

Código: Selecionar todos

WITH recursive Datas AS (
  -- "Membro Âncora"
  SELECT
    1 AS Mes,
    2018 AS Ano,
    1 as Loops
  UNION ALL
    -- "Membro Recursivo"
  SELECT
    CASE
      WHEN Loops % 12 = 0 THEN 1
      ELSE Mes + 1
    end,
    CASE
      WHEN Loops % 12 = 0 THEN Ano + 1
      ELSE Ano
    end,
    Loops + 1
  FROM Datas  /* Resultado da última iteração */
  WHERE
    Loops < 36
)
SELECT  *
  FROM 
  Datas D
  LEFT JOIN
  (
      SELECT
        D_VENC AS VENCIMENTO,
        Coalesce(Sum(apa.vl_guia), 0) AS TOTAL,
        Coalesce(Month(D_VENC),null) AS MES,
        Coalesce(YEAR(D_VENC), null) AS ANO
      FROM cobrancauti APA
      WHERE
        APA.D_PGTO IS NULL
        AND YEAR(APA.D_VENC) BETWEEN 2018
        AND 2020 
       GROUP BY
        year(APA.D_VENC), MONTH(apa.d_venc) ) AS RES ON RES.ANO = D.ANO AND RES.MES  = D.MES

[alxsts]

Olá!

Show!!! Se trocar aqueles NULL por d.mes e d.ano acho que melhora.

[asimoes]

Obrigado Alexandre e Quintas pelas dicas,

Alexandre muito legal esse recurso RECURSIVE, funcionou perfeito

Já removi a GAMBI do código, RS