CODEWITHSUNDEEP
sqloracle-databaseselectsum
user3187985
user3187985

Reputation: 3

(SQL) Struggling with using sum

I'm learning SQL, and struggling with some of the syntax. So, the following SQL code is supposed to display a list of suppliers and the total price of all of their products. It works fine until I try to include the extra condition where I only want it to display the rows where the total sum is less than 100.

SELECT Suppliers.SupplierName, SUM(Products.Price)
FROM Suppliers, Products
WHERE Suppliers.SupplierID = Products.SupplierID
AND Products.Price < 100
GROUP BY Suppliers.SupplierName;

As the code is at the moment, it simply omits individual values larger than 100 from the final total. Changing the code to

AND SUM(Products.Price) < 100

didn't work either. And I'm not sure on how I'm supposed to go about making it work the way I want it too.

Also, using Oracle.

Upvotes: 0

Views: 79

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

If you are learning SQL, you should learn to use explicit join syntax. I think you are looking for the having clause; that is where conditions on aggregated amounts go:

SELECT s.SupplierName, SUM(p.Price)
FROM Suppliers s join
     Products p
     on s.SupplierID = p.SupplierID
GROUP BY s.SupplierName
HAVING SUM(p.Price) < 100;

I also added table aliases to your query (s and p). These typically make a query easier to read, and they are needed for self joins.

Upvotes: 4

sqlab
sqlab

Reputation: 6436

use

WHERE Suppliers.SupplierID = Products.SupplierID
GROUP BY Suppliers.SupplierName
HAVING SUM(Products.Price) < 100;

Upvotes: 0

Ashish Gaur
Ashish Gaur

Reputation: 2050

Try this :

SELECT Suppliers.SupplierName, SUM(Products.Price) totalPrice
FROM Suppliers, Products
WHERE Suppliers.SupplierID = Products.SupplierID
GROUP BY Suppliers.SupplierName
HAVING totalPrice < 100;

Upvotes: 0

Related Questions