Invalid column name on SQL Query [duplicate]

Question

I am currently teaching myself SQL on SQL Server and came across this practice problem but the solution keeps running an invalid column for my column alias

QUESTION

Select the name of each manufacturerer which having an average price above $145 and contain at least 2 different products.

SOLUTION

SELECT M.name, AVG(P.price) AS avgprice, COUNT(p.manufacturer) AS mancount
FROM manufacturers M, products P
WHERE p.manufacturer = m.code
GROUP BY p.manufacturer
HAVING avgprice >= 150 AND mancount >= 2;

Answer 1

You can't reference column aliases in HAVING, only in ORDER BY. This is because the expressions in HAVING are processed before the SELECT list.

Try:

SELECT M.name, 
       AVG(P.price) AS avgprice, 
       COUNT(P.manufacturer) AS mancount
FROM dbo.manufacturers AS M
INNER JOIN dbo.products AS P
  ON P.manufacturer = M.code
GROUP BY P.manufacturer
HAVING AVG(P.price) >= 150 
  AND COUNT(P.manufacturer) >= 2;

A couple of other fixes:

• Don't use old-style joins (FROM a, b)
• Be consistent with case (P <> p on case- or binary-sensitive collations)
• Always reference tables with schema name

Answer 2

You can't reference an alias in the having (or where or group by) clause. You have to repeat the calculation or calculate it in a sub-query e.g.

You also can't select a column you are not grouping or aggregating (p.manufacturer), I think you intended to group by M.[name].

SELECT M.[name], AVG(P.price) AS avgprice, COUNT(P.manufacturer) AS mancount
FROM manufacturers AS M
INNER JOIN products AS P ON P.manufacturer = M.code
--WHERE P.manufacturer = M.code
GROUP BY M.[name]
--HAVING avgprice >= 150 AND mancount >= 2;
-- Repeat the calculation
HAVING AVG(P.price) >= 150 AND COUNT(P.manufacturer) >= 2;

Note: Its best practice to use explicit joins as I have shown rather than a comma join as you have used.

An example of a sub-query solution, which avoids having to repeat the calculation, is:

SELECT [name], avgprice, mancount
FROM (
    SELECT M.[name], AVG(P.price) AS avgprice, COUNT(P.manufacturer) AS mancount
    FROM manufacturers AS M
    INNER JOIN products AS P ON P.manufacturer = M.code
    GROUP BY M.[name]
) AS X
WHERE avgprice >= 150 AND mancount >= 2;