obtener la sumatoria de pedidos unicos

[gerardo_goh]

que tal, tengo este problema, me dieron varios archivos de excel y los estuve ir subiendo a mysql cada uno de estos archivos lo que deseo es obtener dos reportes uno es el resumen de pedido de todos los pedidos con la ultima fecha de reporte y el otro reporte es la sumatoria de pedidos unicos, como es un historico anexo el ejemplo del contenido de la tabla ya que son aproximadamente 2 millones de registros

Código MySQL:

use test;
 
 
create table tblhistorico (fecha_orden     date         not null,
                           fecha_reporte   date         not null,
                           id_cliente      varchar(50)  not null,
                           no_orden        varchar(50)  not null,
                           cantidad        int          not null);
 
 
 
insert into tblhistorico (fecha_orden,fecha_reporte,id_cliente,no_orden,cantidad)
values ('2012/07/03','2012/08/02','JL1','A1',5),
       ('2012/07/03','2012/08/05','JL1','A1',5),
       ('2012/08/03','2012/08/02','JL1','A5',15),
       ('2012/08/03','2012/08/05','JL1','A5',15),
       ('2012/08/03','2012/08/02','JL1','A7',15),
       ('2012/08/03','2012/08/05','JL1','A7',15),
       ('2012/08/03','2012/08/07','JL1','A7',15);
 

quiero obtener dos resultado del ejemplo el primero que me de lo siguiente:

Código MySQL:

fecha_orden|fecha_reporte|id_cliente|no_orden|cantidad
03/07/2012|05/08/2012|JL1|A1|5
03/08/2012|05/08/2012|JL1|A5|15
03/08/2012|07/08/2012|JL1|A7|15
 

y el segundo reporte sería la sumatoria de ese reporte:

Código MySQL:

id_cliente|cantidad
JL1|35
 

Del primer caso del reporte estaba intentando de la siguiente manera pero

Código MySQL:

select fecha_orden,
       fecha_reporte,
       id_cliente,
       no_orden,
       cantidad
from tblhistorico
group by id_cliente,no_orden;
 

Pero no logro obtener del ultima fecha de reporte


Del segundo caso he intentado el rollup para obtener la sumatoria pero no logro hacerlo, ya que suma todo

Código MySQL:

select id_cliente,
       no_orden,
       sum(cantidad)
from tblhistorico
group by id_cliente,no_orden with rollup
 

Ojala me puedan ayudar muchas gracias

[leonardo_josue]

Hola gerardo_goh:

En este foro hemos tratado muchas veces el tema de encontrar el último registro para un cierto grupo de criterios, me sorprende que no hayas encontrado un ejemplo que pudieras aplicar a tu problema. Existen varias formas de hacer esta consulta, una de las más utilizadas sería con una subconsulta y con un INNER JOIN, haciendo lo siguiente:

Código MySQL:

mysql> SELECT * FROM tblhistorico;
+-------------+---------------+------------+----------+----------+
| fecha_orden | fecha_reporte | id_cliente | no_orden | cantidad |
+-------------+---------------+------------+----------+----------+
| 2012-07-03  | 2012-08-02    | JL1        | A1       |        5 |
| 2012-07-03  | 2012-08-05    | JL1        | A1       |        5 |
| 2012-08-03  | 2012-08-02    | JL1        | A5       |       15 |
| 2012-08-03  | 2012-08-05    | JL1        | A5       |       15 |
| 2012-08-03  | 2012-08-02    | JL1        | A7       |       15 |
| 2012-08-03  | 2012-08-05    | JL1        | A7       |       15 |
| 2012-08-03  | 2012-08-07    | JL1        | A7       |       15 |
+-------------+---------------+------------+----------+----------+
7 rows in set (0.00 sec)
 
mysql> SELECT T1.*
    -> FROM tblhistorico T1 INNER JOIN
    -> ( SELECT id_cliente, no_orden, MAX(fecha_reporte) max_fecha_reporte
    ->   FROM tblhistorico
    ->   GROUP BY id_cliente, no_orden) T2
    -> ON T1.id_cliente = T2.id_cliente AND
    ->    T1.no_orden = T2.no_orden AND
    ->    T1.fecha_reporte = T2.max_fecha_reporte;
+-------------+---------------+------------+----------+----------+
| fecha_orden | fecha_reporte | id_cliente | no_orden | cantidad |
+-------------+---------------+------------+----------+----------+
| 2012-07-03  | 2012-08-05    | JL1        | A1       |        5 |
| 2012-08-03  | 2012-08-05    | JL1        | A5       |       15 |
| 2012-08-03  | 2012-08-07    | JL1        | A7       |       15 |
+-------------+---------------+------------+----------+----------+
3 rows in set (0.00 sec)
 

Para la segunda consulta, en realidad podrías basarte en la primera, agregando los criterios de agrupación que necesitas.

Código MySQL:

mysql> SELECT T1.id_cliente, SUM(T1.cantidad)
    -> FROM tblhistorico T1 INNER JOIN
    -> ( SELECT id_cliente, no_orden, MAX(fecha_reporte) max_fecha_reporte
    ->   FROM tblhistorico
    ->   GROUP BY id_cliente, no_orden) T2
    -> ON T1.id_cliente = T2.id_cliente AND
    ->    T1.no_orden = T2.no_orden AND
    ->    T1.fecha_reporte = T2.max_fecha_reporte
    -> GROUP BY T1.id_cliente;
+------------+------------------+
| id_cliente | SUM(T1.cantidad) |
+------------+------------------+
| JL1        |               35 |
+------------+------------------+
1 row in set (0.00 sec)
 

Dale un vistazo, pero sobre todo trata de analizar a detalle cómo están funcionando las consultas, no que quedes solo con copiar y pegar la respuesta. Estoy seguro que si llegas a entenderlas te servirán mucho en el futuro.

Saludos
Leo.

[gerardo_goh]

ok, muchas gracias leonardo, estuve revisando algunos ejemplos del maximo, voy tambien a revisar esta solucion que me das con el inner join, a veces uno se limita cuando la solucion esta enfrente de uno.

Muchas gracias amigo, saludos