Dúvida select agrupando por mês/ano - 2 tabelas

SQL

26/11/2018

Olá pessoas,

Sou novato e preciso de uma ajuda. Tenho 2 tabelas, a primeira de RENDA (id, id_usuario, nome, data, valor, obs) e a segunda DESPESA (id, id_usuario, nome, data, valor, obs).

Eu preciso gerar um gráfico de barras de rendas/despesas agrupados por mês... Por exemplo se eu colocar os últimos 3 meses... eu vou ter setembro, outubro e novembro, sendo que cada mês vou ter uma barra de renda e uma barra de despesa.

Meu SQL
SELECT MONTH(renda.data), SUM(renda.valor), SUM(despesa.valor)
                    FROM renda, despesa
                   WHERE renda.id_usuario = 3
                     AND month(renda.data) >= month(CURRENT_DATE)-2
                     AND renda.id_usuario = despesa.id_usuario
                     AND month(renda.data) = month(despesa.data)
                   GROUP BY month(data)


Ambiente
TABELA RENDA
id id_usuario nome data valor obs
22 3 Renda 6 2018-06-29 123.53
23 3 Renda 11 2018-11-10 34.65
24 3 Renda 9 2018-09-15 54.23

TABELA DESPESA
id id_usuario nome data valor obs
5 3 Despesa 1 2018-10-20 37.58
6 3 Despesa 2 2018-11-14 124.43
7 3 Despesa 3 2018-10-19 42
8 3 Despesa 4 2018-09-10 99.9
9 3 Despesa 5 2018-09-05 69.74
10 3 Despesa 6 2018-08-12 46.54

RESULTADO SQL
MONTH(renda.data) SUM(renda.valor) SUM(despesa.valor)
9 108.45999908447266 169.64
11 34.650001525878906 124.43

Problemas...
1- O primeiro problema é que não tenha nenhum registro de renda em um determinado mês mas tenha registros de despesas... ele não exibe o mês, por exemplo no mês 10 tem registros de despesa mas não tem de renda... eu preciso que em renda fique 0 e mostre despesas...
2- O segundo problema é que por algum motivo ele está somando o mês de novembro errado... deveria ser 54.23.
3- Eu percebi que vou ter que preciso buscar por mês/ano, pois em janeiro de 2019 ele pode agrupar 01/2019 com 01/2018 e não quero isso.

Então.. muitos erros e quero corrigir, já tentei de várias formas mas não estou conseguindo sozinho. Alguém pode me ajudar? (=

OBS: se tiver um erro mto feio no meu sql, ignorem e me falem como deveria ser e o motivo. O q esta errado no meu e como posso melhorar. Obrigado!!!!!
Ian Costa

Ian Costa

Curtidas 0

Respostas

Ian Costa

Ian Costa

26/11/2018

Galera, melhorei um pouco mas ainda acho que não é a melhor forma de resolver... masss eu criei uma tabela de mes ( 1 a 12 ) e fiz o sql assim

SELECT mes.mes, SUM(renda.valor), SUM(despesa.valor)
  FROM mes
  LEFT JOIN renda ON mes.mes = MONTH(renda.data)
   AND renda.id_usuario = 3
  LEFT JOIN despesa on mes.mes = MONTH(despesa.data)
   AND despesa.id_usuario = 3
   WHERE mes.mes <= MONTH(CURRENT_DATE)
  GROUP BY mes.mes
  ORDER BY mes.mes DESC
  LIMIT 3


mes -------- SUM(renda.valor) ------------ SUM(despesa.valor)
11 ------ 34.650001525878906 ------------------124.43
10 ------------------- NULL ----------------------------------- 79.58
9 ------------ 108.45999908447266 ------------------169.64



Melhorou um pouco mas ainda esta com erros e preciso corrigir umas falhas.
1- O primeiro mês (9) referente a renda esta vindo duplicado e ainda não consegui identificar o motivo
2- Preciso agrupar pôr mês e ano, pois em janeiro de 2019 ele pode agrupar 01/2019 com 01/2018 e não quero isso.
3- Outro erro que meu sql atual provoca é que.... Caso o mês seja 01 ( janeiro ) e eu queira pegar os ultimos 3 meses... ele só vai pegar janeiro pois ele tem como base a tabela de mês (1 a 12) que eu criei e não existe registro antes do mes 1, ou seja, ele não volta para o mês 12 e 11.

OBS o ambiente se manteve (tabela de renda e despesa)
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

26/11/2018

A idéia é mais ou menos assim:
- primeiro você obtém os meses em que houve movimentação, seja de renda ou de despesa
(quando digo mês, na verdade a referência é sempre ano/mes)
- depois você obtém os valores acumulados, com base nos meses obtidos

no MSSQLServer, seria algo assim:
SELECT
  tab.ANOMES,
  coalesce((SELECT SUM(r.valor) FROM renda r
            WHERE r.id_usuario = tab.id_usuario and LEFT(CONVERT(VARCHAR(10), r.data, 112),6) = tab.ANOMES),0) vlr_renda,
  coalesce((SELECT SUM(d.valor) FROM despesa d
            WHERE d.id_usuario = tab.id_usuario and LEFT(CONVERT(VARCHAR(10), d.data, 112),6) = tab.ANOMES),0) vlr_despesa
FROM
  (SELECT id_usuario, LEFT(CONVERT(VARCHAR(10), data, 112),6) ANOMES FROM renda
   GROUP BY id_usuario, LEFT(CONVERT(VARCHAR(10), data, 112),6)
   UNION
   SELECT id_usuario, LEFT(CONVERT(VARCHAR(10), data, 112),6) ANOMES FROM despesa
   GROUP BY id_usuario, LEFT(CONVERT(VARCHAR(10), data, 112),6)
   ) tab
WHERE
  tab.id_usuario = 3
  AND tab.ANOMES between LEFT(CONVERT(VARCHAR(10), GETDATE()-60, 112),6) AND LEFT(CONVERT(VARCHAR(10), GETDATE(), 112),6)

no MySQL, deve ser algo assim:
SELECT
  tab.ANOMES,
  coalesce((SELECT SUM(r.valor) FROM renda r
            WHERE r.id_usuario = tab.id_usuario and DATE_FORMAT(r.data, '%Y%m') = tab.ANOMES),0) vlr_renda,
  coalesce((SELECT SUM(d.valor) FROM despesa d
            WHERE d.id_usuario = tab.id_usuario and DATE_FORMAT(d.data, '%Y%m') = tab.ANOMES),0) vlr_despesa
FROM
  (SELECT id_usuario, DATE_FORMAT(data, '%Y%m') ANOMES FROM renda
   GROUP BY id_usuario, DATE_FORMAT(data, '%Y%m')
   UNION
   SELECT id_usuario, DATE_FORMAT(data, '%Y%m') ANOMES FROM despesa
   GROUP BY id_usuario, DATE_FORMAT(data, '%Y%m')
   ) tab
WHERE
  tab.id_usuario = 3
  AND tab.ANOMES between DATE_FORMAT(CURRENT_DATE-60, '%Y%m') AND DATE_FORMAT(CURRENT_DATE, '%Y%m')

GOSTEI 0
POSTAR