Avatar of jsbx
jsbx
Flag for Chile asked on

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

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
Microsoft SQL ServerMicrosoft SQL Server 2005ASP

Avatar of undefined
Last Comment
jsbx

8/22/2022 - Mon
Patrick Matthews

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
jsbx

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

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jsbx

ASKER
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.
Patrick Matthews

Replace:

SUM(i.precio_costo_neto) AS precio_costo_neto

with:

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

ASKER
matthewspatrick: thank you very much, you're a genius.
jsbx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jsbx

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