Gerardo Abdo Gerardo Abdo - 2 months ago 6
MySQL Question

MySQL Group by complex script

I have an script that works perfect, but need to add values from another table
Current script is

select v.id, vm.producto_id, sum(vm.total), count(v.id)
from visita v, reporte r, visitamaquina vm, maquina m,
(select r.id, empleado_id, fecha, cliente_id from ruta r, rutacliente rc where r.id=rc.ruta_id and
fecha>='2016-10-01' and fecha<='2016-10-30' group by fecha, cliente_id, empleado_id) as rem
where rem.fecha=v.fecha and v.cliente_Id=rem.cliente_id and r.visita_id=v.id and vm.visita_id=v.id and m.id=vm.maquina_id
group by vm.visita_id, vm.producto_id


Current Script returns this (I need some extra columns but for this purpose I only leave the ones with issues):

| Producto_Id | Id | Total | count(id) |
|---------------|--------------|-----------|-----------|
| 1 | 31 | 21 | 2 |
| 2 | 31 | 15 | 3 |
| 3 | 31 | 18 | 2 |


Table VisitaMaquina has multiple records for same producto_id
VisitaMaquina has this:

| Producto_Id | Visita_Id | Total |
|---------------|--------------|-----------|
| 1 | 31 | 8 |
| 1 | 31 | 13 |
| 2 | 31 | 9 |


Same situation happens with table called reporteproducto, where multiple times producto_id is repeated.

Table reporteproducto has

| Producto_Id | Visita_Id | Quantity |
|---------------|--------------|-----------|
| 1 | 31 | 4 |
| 1 | 31 | 7 |
| 2 | 31 | 5 |


My previous query works fine, and I just need to get the sum of quantity

I used this Script and this is what I got

select v.id, vm.producto_id, sum(vm.total), sum(quantity), count(id)
from visita v, reporte r, visitamaquina vm, maquina m, reporteproducto rp,
(select r.id, empleado_id, fecha, cliente_id from ruta r, rutacliente rc where r.id=rc.ruta_id and
fecha>='2016-10-01' and fecha<='2016-10-30' group by fecha, cliente_id, empleado_id) as rem
where rem.fecha=v.fecha and v.cliente_Id=rem.cliente_id and r.visita_id=v.id and vm.visita_id=v.id and m.id=vm.maquina_id and rp.visita_Id=v.id and rp.producto_id=vm.producto_id
group by vm.visita_id, vm.producto_id


I got this

|Producto_Id | Visita_Id | Total |Quantity | count(id)
|---------------|--------------|-----------|-----------|-----------|
| 1 | 31 | 42 | 11 | 4 |
| 2 | 31 | 45 | 18 | 6 |
| 3 | 31 | 36 | 44 | 4 |


The desired result is (focus on producto_id=1):

|Producto_Id | Visita_Id | Total |Quantity |
|---------------|--------------|-----------|-----------|
| 1 | 31 | 21 | 11 |
| 2 | 31 | 15 | 18 |
| 3 | 31 | 18 | 44 |


Any Idea on how to solve this?

Answer

Better group the sub table that has multiple data with the same group of your outer group by columns.In your case the VisitaMaquina and reporteproducto should be group by with visita_id, producto_id since they all have repeat rows with the same combination of vid=31 and pid=1.

You can change the visitamaquina vm and reporteproducto rp table alias to sub query form of the following:

(select visita_id, Producto_Id, sum(Total) as Total from visitamaquina 
 group by visita_id, Producto_Id) vm,  
(select Producto_Id, Visita_Id, sum(Quantity) as Quantity from reporteproducto
 group by Producto_Id, Visita_Id) rp

Also I found that there is vm.maquina_id in your where clause, maybe this causes your problem.Because if the visitamaquina and reporteproducto both have repeat values of visita_id, producto_id then the output should have Total, Quantity both doubled.In your output the Quantity is right, that's odd.