CODEWITHSUNDEEP
sql-server
LeeTee
LeeTee

Reputation: 6601

SQL Server select ids where number of rows is above specified amount

I am not very familiar with SQL Server but I know the basics. I want to do something like the below but this does not work.

SELECT COUNT(*) AS Expr2, shopid AS Expr1
FROM Orders
WHERE (Expr2 > '5000')

I basically need to pull out shopIds where they have over 5000 orders.

Upvotes: 0

Views: 124

Answers (1)

StuartLC
StuartLC

Reputation: 107277

Unfortunately, MSSQL won't allow aliases in a WHERE or HAVING clause, so you'll need to repeat the aggregate field. You'll also want to Group by the Shop Id if you want to count the orders per shop:

SELECT     COUNT(*) AS Expr2, 
shopid AS Expr1 
FROM         
Orders 
GROUP BY ShopId
HAVING (COUNT(*) > 5000)

Upvotes: 2

Related Questions