join 4 tables group by one of them and sum the rest

SELECT a.categoria,
    SUM(i.precio_costo_neto) AS precio_costo_neto,
    SUM(dd.utilidad) AS utilidad
FROM ADMIN_V4_CATEGORIA a INNER JOIN
    INVENTARIO i ON a.id_categoria = i.id_categoria_antigua INNER JOIN
    DTE_DETALLE dd ON i.codigo = dd.codigo INNER JOIN
    DTE d ON dd.id_compra = d.id_compra
GROUP BY a.categoria

matthewspatrick:
thanks, but how can i get the report between 2 dates? (fecha_emision), let's say 1/1/2011 to 5/5/2011

Try this:

SELECT 
	a.categoria, 
    SUM(i.precio_costo_neto) AS precio_costo_neto,
    SUM(dd.utilidad) AS utilidad
FROM 
	ADMIN_V4_CATEGORIA a INNER JOIN
    INVENTARIO i 
		ON a.id_categoria = i.id_categoria_antigua INNER JOIN
    DTE_DETALLE dd 
		ON i.codigo = dd.codigo INNER JOIN
    DTE d 
		ON dd.id_compra = d.id_compra
WHERE
	d.fecha_emision BETWEEN '2011-01-01' to '2011-05-05'
GROUP BY 
	a.categoria

Select allOpen in new window

matthewspatrick:

my query is already like this:

sqlstring = "SELECT a.categoria, SUM(i.precio_costo_neto) AS precio_costo_neto, SUM(dd.utilidad) AS utilidad FROM ADMIN_V4_CATEGORIA a INNER JOIN INVENTARIO i ON a.id_categoria = i.id_categoria_antigua INNER JOIN DTE_DETALLE dd ON i.codigo = dd.codigo INNER JOIN DTE d ON dd.id_compra = d.id_compra WHERE (d.fechaemision >= '2012-05-23' and d.fechaemision<= '2012-05-23') and (d.tipodte='33' or d.tipodte='30' or d.tipodte='35' or d.tipodte='39') GROUP BY a.categoria"

i omited something in my first question that i didn't see, i dont know if you want me to make another question to increase the points you tell me. For the sake of mantaining the question in one place i will ask here:

"precio_costo_neto" is the unit price of the item, but I need to multiply by "cantidad" that is in the table "DTE_DETALLE"

DTE_DETALLE            <- sales details (need to got UTILIDAD from gere)
-----------
id_compra              <- sale id
codigo                 <- barcode
utilidad               <- money earned
cantidad              <- quantity            // NEW

"utilidad" is already multiplied by "cantidad" so it's working  ok and do not need to be modifyed.

Replace:

SUM(i.precio_costo_neto) AS precio_costo_neto

with:

SUM(i.precio_costo_neto * dd.cantidad) AS precio_costo_neto

matthewspatrick: thank you very much, you're a genius.
jsbx

Zberteoc: thank you too for the response but i was already-ready with matthew's reply before.