Select com agrupamento

por **asimoes** » 17 Dez 2020 16:15

Pessoal,

Preciso de uma ajuda no retorno de um select com agrupamento, esse select retorna valor por mês usando filtro, acontece que tem mês nesse exemplo que não aparece na sequência,
Na imagem é o mês 4 não tem informação, nesse caso eu queria que ele apareça com valor zero 0,00, a não ser que tenha um tratamento a função que Quintas me passou:


   aDados := { {}, {}, {} }

   FOR EACH oElemento in aDadosCob
      IF oElemento[ "ANO" ] = Year( Date() ) - 2
         nIndice := 1
      ELSEIF oElemento[ "ANO" ] = Year( Date() ) - 1
         nIndice := 2
      ELSE
         nIndice := 3
      ENDIF
      
      aAdd( aDados[ nIndice ], oElemento[ "VALOR" ]  )
         
   NEXT

por **alxsts** » 17 Dez 2020 16:36

Olá!

Experimente Trocar o INNER JOIN pelo LEFT JOIN. Trate o NULL em cada coluna. Exemplo: troque Sum(...) por Coalesce( Sum( apa.vl_guia ),0 ) As Valor

por **JoséQuintas** » 17 Dez 2020 17:23

Se não encontrar outro jeito, uma possibilidade seria criar os meses e usar no select

SELECT ( 1 AS MES, 2 AS MES, 3 AS MES, 4 AS MES, 5 AS MES, 6 AS MES, 7 AS MES ... ) AS MESES

De repente, ter uma tabela de meses com descrição, pra já vir pronto.... quem sabe....

por **asimoes** » 17 Dez 2020 19:35

O que remove o mês é o filtro no where APA.D_PGTO IS NULL

por **asimoes** » 17 Dez 2020 19:42

alxsts escreveu:Olá!

Experimente Trocar o INNER JOIN pelo LEFT JOIN. Trate o NULL em cada coluna. Exemplo: troque Sum(...) por Coalesce( Sum( apa.vl_guia ),0 ) As Valor

Não deu certo

por **JoséQuintas** » 17 Dez 2020 20:17

Me veio na cabeça colocar IF pra somar zero quando NULL, mas pode deixar muito mais demorado porque vai processar tudo.

por **JoséQuintas** » 17 Dez 2020 20:23

SELECT

A.MES, B.VALOR, B.ANO

FROM

( 1 AS MES, 2 AS MES, 3 AS MES, 4 AS MES, 5 AS MES, 6 AS MES, 
7 AS MES, 8 AS MES, 9 AS MES, 10 AS MES, 11 AS MES, 12 AS MES ) AS A

LEFT JOIN 

( SEU SELECT ) AS B ON A.MES = B.MES

ORDER BY MES

por **asimoes** » 17 Dez 2020 21:10

qUINTAS,

De erro de sintaxe aqui

( 1 AS MES, 2 AS MES, 3 AS MES, 4 AS MES, 5 AS MES, 6 AS MES,
7 AS MES, 8 AS MES, 9 AS MES, 10 AS MES, 11 AS MES, 12 AS MES ) AS A

Estou usando MariaDb

por **asimoes** » 17 Dez 2020 23:46

Fiz uma gambi até descobrir como resolver no select, se não acha o mês adiciona ele na HT, assim faz a RmChar funcionar com os 12 meses

  FOR EACH oElemento IN aDadosCob
   
      IF oElemento["ANO"] = Year( Date() ) - 2
         FOR I:=1 TO 12
            nSearch := Hb_aScan( aDadosCob, {|o| o["MES"] = I .AND. o["ANO"] = Year( Date() ) - 2 })
            IF nSearch = 0
               hRecord := {=>}
               hRecord["TOTAL"] := 0.00
               hRecord["MES"]   := I
               hRecord["VALOR"] := 0.00
               hRecord["ANO"]   := Year( Date() ) - 2
               aAdd( aDadosCob, hRecord )
            ENDIF
         NEXT
      ENDIF
      *
      IF oElemento["ANO"] = Year( Date() ) - 1
         FOR I:=1 TO 12
            nSearch := Hb_aScan( aDadosCob, {|o| o["MES"] = I .AND. o["ANO"] = Year( Date() ) - 1 })
            IF nSearch = 0
               hRecord := {=>}
               hRecord["TOTAL"] := 0.00
               hRecord["MES"]   := I
               hRecord["VALOR"] := 0.00
               hRecord["ANO"]   := Year( Date() ) - 1
               aAdd( aDadosCob, hRecord )
            ENDIF
         NEXT
      ENDIF
      *
      IF oElemento["ANO"] = Year( Date() )
         FOR I:=1 TO 12
            nSearch := Hb_aScan( aDadosCob, {|o| o["MES"] = I .AND. o["ANO"] = Year( Date() ) })
            IF nSearch = 0
               hRecord := {=>}
               hRecord["TOTAL"] := 0.00
               hRecord["MES"]   := I
               hRecord["VALOR"] := 0.00
               hRecord["ANO"]   := Year( Date() )
               aAdd( aDadosCob, hRecord )
            ENDIF
         NEXT
      ENDIF
   NEXT
   
   aSort( aDadosCob ,,, {|x,y| StrZero( y["ANO"], 4 ) + ;
                                 StrZero( y["MES"], 2 ) > ;
                                 StrZero( x["ANO"], 4 ) + ;
                                 StrZero( x["MES"], 2 ) } )

por **alxsts** » 18 Dez 2020 01:27

Olá!

asimoes escreveu:De erro de sintaxe aqui

A sintaxe está errada. Tente assim:

select
  *
from (
    select 0 as Mes
    union
    select 1
    union
    select 2
    union
    select 3
    union
    select 4
    union
    select 5
    union
    select 6
    union
    select 7
    union
    select 8
    union
    select 9
    ...
    union
    select 12
  ) As A;

Usando CTE (Common Table Expressions) recursivos
CTEs recursivos estão disponíveis no MySQL 8.0, MariaDB 10.2 e posteriores

Não tenho nem MySQL nem MariaDB. Teste/ajuste o código abaixo:

WITH RECURSIVE Ano AS (
  SELECT
    1 AS mes
  UNION ALL
  SELECT
    mes + 1 AS mes
  FROM Ano
  WHERE
    Ano.mes <= 12
)
SELECT
  ano.mes,
  b.valor,
  b.ano
FROM (
    SELECT
      month(apa.d_venc) as mes,
      sum(apa.vl_guia) as valor,
      year(apa.d_venc) as ano
    FROM CobrancaUti apa
  ) as b ON ano.mes = b.mes
ORDER BY
  b.mes

por **JoséQuintas** » 18 Dez 2020 03:08

alxsts escreveu:A sintaxe está errada

Realmente: do jeito que eu fiz é como se estivesse selecionando 12 campos, e não 12 registros.

por **asimoes** » 18 Dez 2020 09:43

Ainda não foi,

por **asimoes** » 18 Dez 2020 09:50

Executando essa seleção dá erro

por **asimoes** » 18 Dez 2020 10:01

Essa forma funciona, mas como funcionar no que eu preciso?

por **asimoes** » 18 Dez 2020 17:03

Consegui usar a sintaxe RECURSIVE mas não reso0lveu o problema, o que esperava é que os mese 4 e 9 desse exemplo viessem com valor null ou zero

WITH RECURSIVE Ano AS (
 SELECT 1 AS mes, B.MES
 UNION ALL
 SELECT mes + 1 AS mes
 FROM Ano
 WHERE
    Ano.mes <= 12
) 

SELECT
  B.*
FROM (
    SELECT
      year(apa.d_venc) as ano,
      month(apa.d_venc) as mes,
      SUM( apa.vl_guia )
    FROM CobrancaUti apa
      where
      YEAR( D_VENC) = 2018 AND
      APA.D_PGTO IS NULL AND 
      month(apa.d_venc) <= 12
     GROUP BY YEAR( D_VENC), MONTH( D_VENC)  
  ) as b 
  
ORDER BY
ANO, mes

Quem está online