Display product id and total quantity for products that have been sold more than once

Question

Display product id and total quantity for products that have been sold more than once. Consider only those sale instances when the quantity sold was more than 1.

DATABASE Structure given

Salesman (Sid, Sname, Location)
Product (Prodid, Pdesc, Price, Category, Discount)
Sale (Saleid, Sid, Sldate, Amount)
Saledetail (Saleid, Prodid, Quantity)

My answer

SELECT Prodid, MAX(SUM(Quantity)) "QTY_SOLD"
FROM Saledetail
GROUP BY Prodid HAVING Quantity>1;

I am not getting expected output. What's wrong in this query?

EXPECTED OUTPUT

PRODID	QTY_SOLD
102	11

Duplicate of [SQL Query To Obtain Value that Occurs more than once](https://stackoverflow.com/q/6095567/3404097) — philipxy, Jun 05 '22 at 03:10
How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] [mre] — philipxy, Jun 12 '22 at 18:53

score 0 · Answer 1 · answered Jun 04 '22 at 20:47

SELECT Prodid,MAX(SUM(Quantity)) "QTY_SOLD" FROM Saledetail 
where Quantity>1 --** filters data itself before the aggregation
GROUP BY Prodid;  

SELECT Prodid,MAX(SUM(Quantity)) "QTY_SOLD" FROM Saledetail 
GROUP BY Prodid 
HAVING Quantity>1;--** filters data after performing the aggregation

upon the difference between Having & where you can figure out exactly what you want. ** you can add the where clause to filter data first then group it. but you should know that the order is important i.e. where then group then having

@shivanirautela Show "thanks" & that an answer is the best by clicking on the accept checkmark. Not by comments. [Help] When you have enough reputation you can also upvote(/downvote) posts. — philipxy, Jun 12 '22 at 18:52

score 0 · Answer 2 · edited Jun 14 '22 at 02:50

0

SELECT Prodid,SUM(Quantity)"QTY_SOLD"
FROM Saledetail WHERE Quantity>1
GROUP BY Prodid HAVING COUNT (Prodid)>1;

edited Jun 14 '22 at 02:50

Jeremy Caney

7,102
69
48
77

answered Jun 12 '22 at 12:05

shivani rautela

1
1
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Jeremy Caney Jun 14 '22 at 02:50

Display product id and total quantity for products that have been sold more than once

2 Answers2