INNER JOIN sql query with GROUP BY

Asked

Viewed 54 times

1

I have a select that is giving error. The idea is the result show five columns (CODPROD, DESCRPROD, CODLOCAL, DESCRLOCAL, STOCK). I’m extracting four tables (TGFEST, TGFPRO, TGFLOC and TGFGRU) and the Stock column should be added because the tables bring the same product several times with different stock in each line. Thus the objective is to show a product per line with the sum of the stock of this product. In this case I am filtering by a specific location type (EST.CODLOCAL).

Follows the code:

SELECT

PRO.DESCRPROD AS Produto,
SUM(ESTOQUE) AS Estoque,
EST.CODPROD,
EST.CODLOCAL,
LOC.DESCRLOCAL



FROM TGFEST EST 
    INNER JOIN TGFPRO PRO ON (EST.CODPROD=PRO.CODPROD)
    INNER JOIN TGFLOC LOC ON (EST.CODLOCAL=LOC.CODLOCAL)
    INNER JOIN TGFGRU GRU ON (PRO.CODGRUPOPROD=GRU.CODGRUPOPROD)

WHERE EST.CODLOCAL='1010000'

GROUP BY 

PRO.DESCRPROD,
EST.CODPROD

ORDER BY PRO.DESCRPROD

Follows error: ORA-00979: not a GROUP BY expression

If anyone can help, Mario

sql group-by inner-join

  • In Oracle the group by must contain all select columns (apart from aggregators).

  • @Motta grateful for the clarification that was fundamental for me to have managed to finish my activity. Thank you!

1 answer

0

I’m gonna leave it here because it might help someone who has the same question I did. With the guidance of Motta, I made the adjustment and changed small details and select was as I needed.

Follow the corrected select:

SELECT

EST.CODPROD,
PRO.DESCRPROD,
EST.CODLOCAL,
LOC.DESCRLOCAL,
SUM(EST.ESTOQUE) AS ESTOQUE


FROM TGFEST EST 
    INNER JOIN TGFPRO PRO ON (EST.CODPROD=PRO.CODPROD)
    INNER JOIN TGFLOC LOC ON (EST.CODLOCAL=LOC.CODLOCAL)
    INNER JOIN TGFGRU GRU ON (PRO.CODGRUPOPROD=GRU.CODGRUPOPROD)

WHERE EST.CODLOCAL='1010000'

GROUP BY 
EST.CODPROD,
PRO.DESCRPROD,
EST.CODLOCAL,
LOC.DESCRLOCAL


ORDER BY DESCRPROD

Gratefully, Mário

Browser other questions tagged sql group-by inner-join

You are not signed in. Login or sign up in order to post.