Why can't I apply a criteria on a sub-query?

Question

What I'm trying to do is this:

select Store.Id, (select COUNT(*) from StoreProduct
where StoreProduct.Store_id = Store.Id) as _count from Store where _count > 3

SQL Server says it's invalid because the column name '_count' is invalid. Why it's invalid if I'm declaring it?

Answer 1

Try using a name that begins with a letter instead of an underscore.

Answer 2

Why don't you try this?

select Store_id as Id, COUNT(1) as acount
from StoreProduct
group by Store_id
having count(1) > 3

Answer 3

It's invalid to the WHERE clause because of the order of evaluation in SQL. The WHERE clause is second in the list, so can only see items present in the FROM clause (which is evaluated first). This old article has a breakdown of the evaluation order. I'm sure there's a newer one out there.

Answer 4

select Id as StoreId, count(*) as ProductCount
from StoreProduct
group by Id
having count(*) > 3

---

Another way

select S.Id, count(SP.ProductId) as ProductCount
from Store S
left join StoreProduct SP on (SP.Store_id = S.Id)
group by S.Id
having COUNT(SP.ProductId) > 3

Which, assumes, the unique column in your StoreProduct table is named ProductId

Answer 5

Why not try this:

select a.StoreId,count(*) as TotProducts
from Store a
join StoreProduct b on b.storeId=a.storeID
group by a.storeID
having count(*) > 3

It appears that is what you are trying to accomplish?

Answer 6

'as' is used to logically rename a field within a query. You can't use it to rename a table, which is what your subquery returns, even though we know by context that the table returned by the subquery will only contain a single row.

I hope this helps.