"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

December 17, 2009

Each GROUP BY expression must contain at least one column that is not an outer reference.

Today I ended up with this error. I got the solution from blog link. It solved my problem. Need to learn More :)

Example below
CREATE TABLE TESTError

(
  A INT,
  B INT,
  C INT
)

INSERT INTO TESTError (A,B,C)
VALUES(1,2,3)
INSERT INTO TESTError (A,B,C)
VALUES(2,3,4)
INSERT INTO TESTError (A,B,C)
VALUES(2,4,5)
INSERT INTO TESTError (A,B,C)
VALUES(2,2,2)


--Error Case
SELECT COUNT(A), COUNT(B), COUNT(C), 'Action' = CASE WHEN A < 1 THEN 'ONE'
WHEN A > 1 THEN 'TWO'
END
FROM TESTError
GROUP BY A, B, C,'Action'

Error - Each GROUP BY expression must contain at least one column that is not an outer reference.


--Working Case
SELECT COUNT(A), COUNT(B), COUNT(C), 'Action' = CASE WHEN A < 1 THEN 'ONE'
WHEN A > 1 THEN 'TWO'
END
FROM TESTError
GROUP BY A, B, C

Reason - Column I was selecting is Group BY is a Constant Value which is already derived in Expresession

No comments: