CODEWITHSUNDEEP
sql-server-2008
Spidy
Spidy

Reputation: 39992

SQL query use WHERE on a column ALIAS

This should be easy but I'm getting an "Invalid column Name" error.

SELECT Transaction, COUNT(ItemId) AS ItemCount
FROM TransactionTable
WHERE ItemCount > 5
GROUP BY Transaction
ORDER BY ItemCount DESC

This statement works without the WHERE clause which seems like a double standard to me since ORDER BY is using the column alias.

Upvotes: 2

Views: 4840

Answers (4)

Sascha
Sascha

Reputation: 2213

WITH tempTable AS
(
  SELECT Transaction, COUNT(ItemId) AS ItemCount
  FROM TransactionTable
)
SELECT * 
FROM tempTable
WHERE ItemCount > 5
GROUP BY Transaction
ORDER BY ItemCount DESC

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452978

You can reference the column alias in the ORDER BY as (logically) this is processed after the SELECT. To use a column alias in a WHERE or HAVING clause you can define the SELECT inside a table expression e.g. as below.

;WITH cte
     AS (SELECT Transaction ,
                COUNT(itemid) AS itemcount
         FROM   transactiontable
         GROUP  BY Transaction )
SELECT Transaction ,
       itemcount
FROM   transactiontable
WHERE  itemcount > 5
ORDER  BY itemcount DESC

Upvotes: 0

Phil Helix
Phil Helix

Reputation: 3723

SELECT [Transaction], COUNT(ItemId) AS ItemCount
FROM TransactionTable
WHERE ItemCount > 5
GROUP BY COUNT(ItemId) 
ORDER BY COUNT(ItemId) DESC

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134943

use HAVING and specify count again

SELECT Transaction, COUNT(ItemId) AS ItemCount
FROM TransactionTable
GROUP BY Transaction
HAVING COUNT(ItemId) > 5
ORDER BY ItemCount DESC

The reason ORDER BY is working with the alias is because a SQL query is processed in this order

FROM (incl joins)
ON
OUTER
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP

Upvotes: 6

Related Questions