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

jsbx
jsbx used Ask the Experts™
on
Hello,

I have 4 tables:

DTE                    <- sales main (need to got the date from here)
-----------
id_compra              <- sale id                
fecha_emision          <- date
tipodte                <- kind of transaction  


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


INVENTARIO             <- inventory
----------
codigo                 <- barcode
precio_costo_neto      <- money cost
id_categoria_antigua   <- family id


ADMIN_V4_CATEGORIA     <- product family
------------------
id_categoria           <- family id      
categoria              <- family name


"id_compra" is the same in DTE and DTE_DETALLE
"codigo" is the same in DTE_DETALLE and INVENTARIO
"id_categoria" and "id_categoria_antigua" are the same in INVENTARIO and ADMIN_V4_CATEGORIA tables

I need to select sales (DTE_DETALLE) between 2 given dates (DTE got the date) and end up with this table:

| CATEGORIA (group by this) | PRECIO_COSTO_NETO (sum) | UTILIDAD (sum) |

thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
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

Author

Commented:
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

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Top Expert 2010
Commented:
Sorry about omitting the date :)

Anyway, do be careful if fecha_emision includes a time portion.  If you wanted to include, say, all sales for 1 Jan 2011 through 5 May 2011 irrespective of time, than just using "BETWEEN '2011-01-01' and '2011-05-05'" might leave out sales that actually occurred on 5 May, because of the time portion.  In such a case:

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 >= '2011-01-01' AND 
    d.fecha_emision < '2011-05-06'
GROUP BY a.categoria

Open in new window

Author

Commented:
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.
Top Expert 2010

Commented:
Replace:

SUM(i.precio_costo_neto) AS precio_costo_neto

with:

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

Author

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial