How can I group my records by name in Oracle PL / SQL

0

This is my SQL query as I could group by only the names to retrieve the Top of sold items

SELECT
A.ART_DESCRIPCION,
SUM(VD.VD_CANTIDAD),
SUM(VD.VD_TOTAL_GS)
FROM VENTAS V, VENTAS_DETALLE VD, CLIENTE C, ARTICULO A
WHERE
V.VEN_NRO = VD.VD_NRO
AND
V.VEN_CLIEN = C.CLI_CODIGO
AND
VD.VD_ARTICULO = A.ARTI_CODIGO

GROUP BY A.ART_DESCRIPCION, VD.VD_CANTIDAD, VD.VD_TOTAL_GS

ORDER BY VD.VD_CANTIDAD DESC
    
asked by ArielGamer - 004 12.11.2018 в 15:01
source

1 answer

1

Your first mistake is to include in the group by clause fields for which you do not really want to group. Leave only the field of the description in the grouper.

The second error is that you try to sort by the quantity field, but that field does not exist in the result, and therefore you can not sort by it. The one that exists in the result is the aggregate of the sum of the quantities. You can sort by that field if you want to go from the best seller to the least sold.

We are in 2018, long ago the databases comply with the minimums of the SQL standard, but you keep typing queries in the style of too old versions. I suggest you learn and write queries using the syntax of the inner join, which are much easier to understand.

Finally, your most serious mistake is that you are not understanding the query that you write. I know that you are probably basing yourself on a query that already exists and making adaptations to it. That's fine, we've all done it and it's part of the learning, but you should make an effort to understand every detail of the query if you want to improve your SQL skills.

In this, for example, the fact that you have left the reference to the table cliente , when it is not necessary neither for the results, nor for the relationship of the tables, is a faithful witness of this lack of understanding. (I can not say it's lack of effort, but I think you should try harder).

For that reason, I give you a query that should work, but I have rewritten it with the new syntax. It is up to you to try to understand it.

select   a.art_descripcion
       , sum(vd.vd_cantidad) Cantidad
       , sum(vd.vd_total_gs) TotalGS
  from ventas v
       inner join ventas_detalle vd on v.ven_nro = vd.vd_nro
       inner join cliente c on v.ven_clien = c.cli_codigo
       inner join articulo a on vd.vd_articulo = a.arti_codigo
group by a.art_descripcion
order by sum(vd.vd_cantidad) desc
    
answered by 13.11.2018 в 19:01