CODEWITHSUNDEEP
sql
Devanshu
Devanshu

Reputation: 933

SQL query to find sum using group by

SQL query to find sum of quantity of similar prodid only if quantity in each prodid greater than 1. SQL query:

Select ProdID,sum(quantity) 
  From product 
 Where quantity >1 
 Group by ProdID

What logical error in above query ?? enter image description here

The Result should be :

ProdID  Quantity
------  --------
 102      11

Upvotes: 0

Views: 76

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65343

For such purpose include also quantity column in the group by expression with having clause

Select ProdID,quantity
  from product 
 group by ProdID, quantity
 having sum(quantity) >1

Edit ( due to your last comment ) : Use not in as below 

Select ProdID, sum(quantity) 
  from product 
 where ProdID not in ( select ProdID from product p where quantity = 1 ) 
 group by ProdID

Rextester Demo

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270311

You can use filtering in the having:

Select ProdID, sum(quantity)
from product
group by ProdID
having min(quantity) > 1;

The use of min() assumes that quantity is non-negative.

Upvotes: 1

Related Questions