CODEWITHSUNDEEP
sqloracle-database
blyter
blyter

Reputation: 121

How do I group together similar data in a joined table in Oracle?

This is my query:

SELECT invoice.invno, invoice.invdate, invprod.partno, orderprod.orderprice 
invprod.shipqty
FROM sh_invoice invoice INNER JOIN sh_invprod invprod
    ON invoice.invno = invprod.invno
INNER JOIN sh_orderprod orderprod 
    ON orderprod.partno = invprod.partno
ORDER BY invoice.invno

And here is a snippet of the output:

000299   17-10-15 G61631     998.99          1
000299   17-10-15 G61631     998.99          1
000299   17-10-15 G61631     998.99          1
000299   17-10-15 E42364        180         10
000299   17-10-15 G61631     998.99          1
000299   17-10-15 G61631     998.99          1
000299   17-10-15 G61631     998.99          1

How can I group together the similar partnumbers (G61631) into a single field so it gives a total of the price and total of the shipqty? What I'm planning to do is then find the subtotal of each part, and find the total of each invoice afterwards.

Thanks.

Upvotes: 1

Views: 29

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133390

You ca use sum and group by 

  SELECT 
      invoice.invno
      , invoice.invdate
      , invprod.partno
      , orderprod.orderprice 
      , sum(invprod.shipqty)
  FROM sh_invoice invoice INNER JOIN sh_invprod invprod
      ON invoice.invno = invprod.invno
  INNER JOIN sh_orderprod orderprod 
      ON orderprod.partno = invprod.partno

  GROUP BY invoice.invno
      , invoice.invdate
      , invprod.partno
      , orderprod.orderprice 
  ORDER BY invoice.invno

Upvotes: 1

Related Questions