Difference between standard price and the overall average standard price of all products

0
votes

I'm running this query basically word for word from the book.

Provide a listing of all of the products that includes the difference between its standard price and the overall average standard price of all products.

SELECT ProductStandardPrice – PriceAvg AS Difference
FROM Product_T, (SELECT AVG(ProductStandardPrice) AS PriceAvg
FROM Product_T);

I keep getting this error:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ';'.

1
sqlsql-server
Your error is a SQL Server error; only tag with the database you are really using. – Gordon Linoff

1 Answers

2
votes

Your specific error is because you don't have a table alias. I also abhor commas in the FROM clause, so:

SELECT (t.ProductStandardPrice – a.PriceAvg) AS Difference
FROM Product_T t CROSS JOIN
     (SELECT AVG(ProductStandardPrice) AS PriceAvg
      FROM Product_T
     ) a;

However, the best way to answer this question is to use window functions:

SELECT (t.ProductStandardPrice – AVG(t.ProductStandardPrice) OVER () 
       ) AS Difference
FROM Product_T t ;