Jose Jose - 7 months ago 21
SQL Question

MySQL Query with LEFT JOIN, SUM and GROUP BY

I tried Create View in MySQL but I do not get what I need.
I have two tables


  • servicios: id_servicos, fecha, cantidad

  • actuacion: id_actuacion, clv_servicio, grupo



So if the servicios table had the rows:

1, 2016-05-02, 2
2, 2016-05-03, 3
3, 2016-05-03, 5

actuacion table rows
1, 1, RED
2, 1, RED
3, 2, BLUE
4, 4, ORANGE
5, 3, RED


and I want the output to look similar to this

RED, 7
BLUE, 2
ORANGE, 4


So my query is looking like this

Select actuacion.grupo,
Sum(servicios.cantidad) As total
From (actuacion
Join servicios On actuacion.clv_servicio = servicios.id_servicos)
Group By actuacion.grupo


and the result is

RED, 9
BLUE, 2
ORANGE, 4


I think you have to match before dates(fecha) to then the sum of the quantities(cantidad) and so do not add twice the RED group
I need the result of RED is 7

Any help is greatly appreciated.

jpw jpw
Answer

You can find the unique combinations in the actuacion table in a query used as a derived table and join with that.

This should give you the result you want:

Select t.grupo,
       Sum(servicios.cantidad) As total
From (select distinct clv_servicio, grupo from actuacion) as t
Join servicios On t.clv_servicio = servicios.id_servicos
Group By t.grupo