1

I have the following MySQL query

select 
    keliones_lapas.Vairuot_Id, 
    MONTH(keliones_lapas.Data_darbo), 
    sum(keliones_lapas.uzdarbis) as Got, 
    coalesce(Suma, 0) Suma, 
    (sum(keliones_lapas.uzdarbis) - coalesce(Suma, 0)) Total
from keliones_lapas
left join (select Vairuotas, MONTH(Data_islaidu), sum(Suma) as Spend 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);

And i'm getting this error:

1054 - Unknown column 'Suma' in 'field list'

I know that this issue is known but i do not know how to fix it by my self. This is the cause of the issue which is solved

And i can provide with the fiddle : http://sqlfiddle.com/#!9/e76ef9/8

Community
  • 1
  • 1
Imantas Balandis
  • 89
  • 1
  • 9

1 Answers1

3

You just need to alias all of the calculated columns in the sub-query, and then you can reference those aliases in the ON clause. Here is the SQL from your fiddle with the Data_islaidu and Suma columns aliased:

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);
zambonee
  • 1,599
  • 11
  • 17
  • Thank you that solved the errors!! Could you look into my sql and see why he does not count Spend? – Imantas Balandis Dec 16 '17 at 12:04
  • It could be because islaidos.Suma has a lot of nulls. Try SUM(COALESCE(Suma, 0)) AS Suma in your subquery to get 0's instead of nulls. – zambonee Dec 18 '17 at 16:42