Avatar of jsbx
jsbx
Flag for Chile asked on

group by errors

sqlstring = "SELECT a.id_categoria, a.categoria, SUM(i.precio_costo_neto * dd.cantidad) 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 >= '" & fecha_inicio & "' and d.fechaemision<= '" & fecha_termino & "') and (d.tipodte='33' or d.tipodte='30' or d.tipodte='35' or d.tipodte='39') GROUP BY a.id_categoria, a.categoria order by a.categoria"

I just want to group by a.id_categoria, but if i remove the a.categoria from the order by clause it gives me an error and i need to use it.

Also i would like to know if this "GROUP BY a.id_categoria, a.categoria order by a.categoria" will give me different results that if i only group by id_Categoria


thanks.
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
jsbx

8/22/2022 - Mon
Ken Butters

I repasted your query below, reformatted to be easier to read...

The select statement is selecting 4 fields.

a.id_categoria,
a.categoria
SUM(i.precio_costo_neto * dd.cantidad) AS precio_costo_neto
SUM(dd.utilidad) AS utilidad

The groupBy clause is indicating which records you want added together to become the sum.  Since you have two fields that are NOT part of a sum... then they should be part of the group by.

If you want to groupby only a.id_categoria, then you need to remove a.categoria  from the topmost select.

SELECT
	a.id_categoria,
	a.categoria,
	SUM(i.precio_costo_neto * dd.cantidad) 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 >= '" & fecha_inicio & "' AND d.fechaemision <=
		'" & fecha_termino & "'
	)
AND
	(
		d.tipodte = '33' OR d.tipodte = '30' OR d.tipodte = '35' OR d.tipodte = '39'
	)
GROUP BY
	a.id_categoria,
	a.categoria
ORDER BY
	a.categoria

Open in new window


To answer your question... yes you would get different answers with the different group by clauses.

Giving you an example.... suppose you have two characteristics.... Shape and Color

If you group by only Shape... then the SUM's that you calculate, will be by Shape only... for example how many Squares do I have.

If you group by Shape and Color... then the sums will be by shape and color... for example how many Red Squares... how many Blue squares etc...
jsbx

ASKER
buttersk:

If I remove a.categoria from the topemost select how can i get that field data?
I need it.

categoria is the "human readable" name of id_categoria and i need to put it in the report.

thanks
ASKER CERTIFIED SOLUTION
Ken Butters

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
Ken Butters

Adding to my previous Shape / Color illustration... it's more like you have Shape in English, and Shape in Spanish.

Since they are both shape, and the both really refer to the same characteristic... the final groups they end up in ... are the same.

Hope that helps.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
jogos

Off topic: why the dynamic sql? Passing values can be done by using parameters.
jsbx

ASKER
thank you.