group by errors

jsbx
jsbx used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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...

Author

Commented:
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
If a.id_categoria, a.categoria are both different versions of the same thing (one human readable, and the other some sort of code)... then you can leave them both in the group by field and it won't hurt anything, and it won't change your result set.

As a matter of fact if you want them both in your select... then you HAVE to have them in your group by.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.

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

Author

Commented:
thank you.

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