Multi tool use

GROUP BY in a FULL OUTER JOIN?

I am working on two tables

PRODUCT table
with
PRODUCTID, PRICE

and I am working with

ORDERLINE table
which has
PRODUCTID, QUANTITY, ORDERID

I want to join both tables so that I have

ORDERQUANTITY
which has
PRODUCT.PRODUCTID, ORDERLINE.QUANTITY
GROUPED BY
PRODUCTID

In other words I would like the two tables not to be like this:

PRODUCTID QUANTITY
- 10 4
- 10 2
- 20 1
- 20 6
- 30 4
- 30 6
- 30 2
- 30 2
- 40 2
- 40 2
- 40 5


But like this

PRODUCTID QUANTITY
- 10 6
- 20 7
- 30 14
- 40 9


My current code shown below only does the first table when I try to group by productid it won't work.

SELECT PRODUCT.PRODUCTID, ORDERLINE.QUANTITY
FROM ORDERLINE
FULL OUTER JOIN PRODUCT ON PRODUCT.PRODUCTID = ORDERLINE.PRODUCTID
ORDER BY PRODUCT.PRODUCTID;


2 Answers
2

I'm not sure about understanding your question, but I think that this sentece should give you the results you are asking for:

SELECT PRODUCT.PRODUCTID, SUM(ORDERLINE.QUANTITY) AS QUANTITY
FROM ORDERLINE
FULL OUTER JOIN PRODUCT ON PRODUCT.PRODUCTID = ORDERLINE.PRODUCTID
GROUP BY PRODUCT.PRODUCTID
ORDER BY PRODUCT.PRODUCTID;


You don't need the join if you need only id and sum of ordered quantity :

select PRODUCTID, sum(QUANTITY)
from ORDERLINE
group by PRODUCTID;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.