Simple MS SQL Bug

I am very new to MS SQL and am trying to get all duplicate emails in a table. I found a similar query that works for MySQL but when run it in MS SQL I get the error Invalid column name 'ct'.

The query I开发者_StackOverflow社区 run is:

SELECT   
  [ShipEmail], 
  COUNT(*) as ct

FROM [ShipWorksDefault].[dbo].[Customers] GROUP BY [ShipEmail] HAVING [ct] > 1

I am guessing there is something very simple I am over looking.

Many thanks in advance!

Just change the HAVING clause to not use the column alias:

HAVING count(*) > 1

You can't use the alias in the having clause. Try:

SELECT   
  [ShipEmail], 
  COUNT(*) as ct

FROM [ShipWorksDefault].[dbo].[Customers] GROUP BY [ShipEmail] HAVING COUNT(*) > 1

SELECT   
  [ShipEmail], 
  COUNT(*) as ct
FROM [ShipWorksDefault].[dbo].[Customers] 
GROUP BY [ShipEmail] HAVING COUNT(*) > 1

or

SELECT   
  [ShipEmail], 
  COUNT(*) as ct
FROM [ShipWorksDefault].[dbo].[Customers] 
GROUP BY [ShipEmail] HAVING COUNT([ShipEmail]) > 1