Showing Invalid Column Name

Question

Hi experts,

I am having difficulty running my code. I have an aggregate function AVG and when I'm running the code, I am getting an invalid column name.

SELECT  
	A.ID,  
	A.Column1,  
	A.Column2,  
	B.Column3,  
	B.Column4,  
	CASE WHEN A.Column5 IS NULL THEN 'Yes' ELSE 'No' END AS "Checker",   
	AVG(CAST(C.Column6 AS DECIMAL(10,4))) AS "Indicator A",   
	AVG(CAST(C.Column7 AS DECIMAL(10,4))) AS "Indicator B"  
	  
	FROM TABLE_A A  
	JOIN TABLE_B B  
	ON A.ID = B.ID  
  
	JOIN TABLE_C C  
	ON A.ID = C.ID  
	  
	GROUP BY ID, A.Column1, A.Column2, B.Column3, B.Column4  
	HAVING ("Indicator A" > 0) OR ("Indicator A" = 0 AND "Indicator B" = 0)  

It's showing invalid column name 'Indicator A' and 'Indicator B'. I think my problem lies on how use GROUP BY and HAVING.

Thank you.

Answer 1

Hi @Sherlan Emmanuel Burgonia
(1) As experts answered, the alias such as "Indicator A" was added in SELECT clause which is Step 5. Therefore, you cannot use these aliases in Step 3 (Group By) or Step 4 (Having).
Solution 1: Use full expression instead of alias in HAVING clause. Like this:

HAVING (AVG(CAST(C.Column6 AS DECIMAL(10,4))) > 0)   
    OR (AVG(CAST(C.Column6 AS DECIMAL(10,4))) = 0 AND AVG(CAST(C.Column7 AS DECIMAL(10,4))) = 0)  

Solution 2: Use subquery or CTE. Refer to Erland's answer.
(2) Regarding the GROUP BY clause, you have A.Column5 in SELECT clause, while it is not contained in either an aggregate function or the GROUP BY clause.
Try this query:

;WITH CTE AS  
( SELECT A.ID,A.Column1,A.Column2,B.Column3,B.Column4,  
         CASE WHEN A.Column5 IS NULL THEN 'Yes' ELSE 'No' END AS Checker,   
         CAST(C.Column6 AS DECIMAL(10,4)) AS Column6,  
         CAST(C.Column7 AS DECIMAL(10,4)) AS Column7  
  FROM TABLE_A A JOIN TABLE_B B ON A.ID = B.ID  
                 JOIN TABLE_C C ON A.ID = C.ID  
)  
SELECT ID,Column1,Column2,Column3,Column4,Checker,  
       AVG(Column6) AS "Indicator A",  
	   AVG(Column7) AS "Indicator B"  
FROM CTE  
GROUP BY ID,Column1,Column2,Column3,Column4,Checker  
HAVING (AVG(Column6) > 0) OR (AVG(Column6) = 0 AND AVG(Column7) = 0)  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The logical evaluation of a SELECT query is in the order:

1. FROM-JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Thus, you cannot use aliases in the SELECT list anywhere but in the ORDER BY clause. You would need to repeat the AVG expressions in the HAVING clause.

Alternatively, you can introduce a Common Table Expression, CTE:

   ; WITH CTE AS  
        SELECT  
            A.ID,  
            A.Column1,  
            A.Column2,  
            B.Column3,  
            B.Column4,  
            CASE WHEN A.Column5 IS NULL THEN 'Yes' ELSE 'No' END AS "Checker",   
            AVG(CAST(C.Column6 AS DECIMAL(10,4))) AS "Indicator A",   
            AVG(CAST(C.Column7 AS DECIMAL(10,4))) AS "Indicator B"  
                 
            FROM TABLE_A A  
            JOIN TABLE_B B  
            ON A.ID = B.ID  
             
            JOIN TABLE_C C  
            ON A.ID = C.ID  
                 
            GROUP BY ID, A.Column1, A.Column2, B.Column3, B.Column4  
   )  
   SELECT ID, Column1, Column2, Column3, Column4, Checker, "Indicator A", "Indicator B"  
   FROM CTE  
   WHERE ("Indicator A" > 0) OR ("Indicator A" = 0 AND "Indicator B" = 0)  

Answer 3

HAVING ("Indicator A" > 0) OR ("Indicator A" = 0 AND "Indicator B" = 0)

You can not column alias defined in SELECT clause in a HAVING clause.
You either have to use the same expression or use a CTE.

Answer 4

Hi @LiHongMSFT-4306 and @Erland Sommarskog both worked well. Thank you very much!