CODEWITHSUNDEEP
sql
Randy B.
Randy B.

Reputation: 453

SQL: Using a subquery to compare Averages

I've been trying to set up this query.

SELECT P.ProductNo,P.Description, AVG(OrderLine.ActualPrice)
FROM Product P
INNER JOIN OrderLine
     ON OrderLine.ProductNo = P.ProductNo
WHERE AVG(ActualPrice) > 
                 (SELECT AVG(ActualPrice)
                          FROM OrderLine)

My goal here is to compare the average actual price for a product to the average price of all products together. But I don't seem to be getting it to work. Where am I going wrong here?

Upvotes: 1

Views: 2380

Answers (1)

John Woo
John Woo

Reputation: 263703

  • use GROUP BY
  • when using aggregated condition, use HAVING instead of WHERE

query,

SELECT  P.ProductNo, P.Description, AVG(OrderLine.ActualPrice)
FROM    Product P
        INNER JOIN OrderLine
              ON OrderLine.ProductNo = P.ProductNo
GROUP BY P.ProductNo, P.Description
HAVING AVG(ActualPrice) > (SELECT AVG(ActualPrice)  FROM OrderLine)

Upvotes: 3

Related Questions