CODEWITHSUNDEEP
sqlgroup-bysum
Imantas Balandis
Imantas Balandis

Reputation: 89

What is wrong with my SQL query SUM group-by

Hello i have a sql query and it does not count my one row. which is called Spend, you can see it in the fiddle. what is wrong with my code? I just need basic table 

Month  ID GOT SPEND
1      1  100 50
2      1  500 200
1      2  200 50

I have created the fiddle http://sqlfiddle.com/#!9/3623b1/2

Could you please help me?

Here is the query: 

select 
    keliones_lapas.Vairuot_Id, 
    MONTH(keliones_lapas.Data_darbo), 
    sum(keliones_lapas.uzdarbis) as Got, 
    coalesce(Suma, 0) AS Spend, 
    (sum(keliones_lapas.uzdarbis) - coalesce(Suma, 0)) Total
from keliones_lapas
    left join (
        select Vairuotas, 
               MONTH(Data_islaidu) as Data_islaidu, 
               sum(Suma) as Suma 
   from islaidos 
   group by Vairuotas, MONTH(Data_islaidu)) islaidos 
   on keliones_lapas.Vairuot_Id = islaidos.Vairuotas 
   and MONTH(keliones_lapas.Data_darbo) = MONTH(islaidos.Data_islaidu)
group by keliones_lapas.Vairuot_Id, MONTH(keliones_lapas.Data_darbo), Suma
order by keliones_lapas.Vairuot_Id, MONTH(keliones_lapas.Data_darbo);

Upvotes: 0

Views: 38

Answers (1)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: You are taking already month in your subquery then again using MONTH to retrieve from month in the join so it's returning NULL and not matching with any month of keliones_lapas 

SELECT 
    keliones_lapas.Vairuot_Id, 
    MONTH(keliones_lapas.Data_darbo), 
    SUM(keliones_lapas.uzdarbis) AS Got, 
    COALESCE(Suma, 0) AS Spend, 
    (SUM(keliones_lapas.uzdarbis) - COALESCE(Suma, 0)) Total
FROM keliones_lapas
    LEFT JOIN (
    SELECT Vairuotas, 
           MONTH(Data_islaidu) AS Data_islaidu, --It's already in MONTH
           SUM(Suma) AS Suma 
   FROM islaidos 
   GROUP BY Vairuotas, MONTH(Data_islaidu)) islaidos 
   ON keliones_lapas.Vairuot_Id = islaidos.Vairuotas 
   AND MONTH(keliones_lapas.Data_darbo) = Data_islaidu --No need to use MONTH or `vice versa`
GROUP BY keliones_lapas.Vairuot_Id, MONTH(keliones_lapas.Data_darbo), Suma
ORDER BY keliones_lapas.Vairuot_Id, MONTH(keliones_lapas.Data_darbo)

Upvotes: 1

Related Questions