Use a query's value in where statement

Question

I'm searching how to do this and can't find it.

Here's my query:

SELECT
    S_Product.Name AS "Name"
    SUM(S_Item.Quantity) AS "QtySold"
FROM s_product
INNER JOIN S_Item ON S_Product.ID = S_Item.Product_ID
WHERE "QtySold" > 500 
--(SUM(S_Item.Quantity)) cannot be used in a WHERE statement since it's a group function
GROUP BY S_Product.Name

Though this doesn't work I think you get my idea, how could I use the `"QtySold" value in the WHERE statement?

Answer 1

The answer is to put any testing of aggregations in to a HAVING clause:

SELECT
    S_Product.Name AS "Name"
    SUM(S_Item.Quantity) AS "QtySold"
FROM s_product
INNER JOIN S_Item ON S_Product.ID = S_Item.Product_ID
GROUP BY S_Product.Name
HAVING SUM(S_Item.Quantity) > 500 ;

Answer 2

Depending on what your SQL server can do, there are other ways to use a query result in a condition of another query, such as.

SELECT * FROM Orders WHERE CustomerID IN
    ( SELECT CustomerID FROM Customers WHERE ExpressDelivery = 1 )
;
SELECT SomeSubQuery.column1
FROM
    (
    SELECT SUM(Quantity) AS column1, Name AS Name
    FROM s_product GROUP BY Name
    ) AS SomeSubQuery  /* needs a name this time */
;
WITH SomeQuery (Quantities, Name) AS
( SELECT SUM(Quantity) AS Quantities, Name 
  FROM s_product GROUP BY Name )
SELECT Quantities, Name FROM SomeQuery WHERE Quantities > 500
;

The last 2, especially, may be particular to Microsoft SQL Server? (Update) Or may work generally.

I wrote a poem about my first example:

Expedite supply
When prime customers desire
Seasonal produce.

I want a server that can execute that. (but, would it want me?)