CODEWITHSUNDEEP
sql-servercount
Kalessin
Kalessin

Reputation: 2302

Conditional SELECT COUNT(id) using COUNT(id) as the condition?

I want to select only the category IDs where the number of articles assigned to the category is more than 3.

I've tried various permutations and posted the one below that I feel most clearly explains what I'm trying to do, although I have a feeling I need to use a nested SELECT. I have tried nested SELECTs but with no success.

SELECT categoryID, COUNT(articleID) AS numArticles 
FROM articles GROUP BY categoryID WHERE numArticles > 3

Upvotes: 0

Views: 5766

Answers (6)

Femaref
Femaref

Reputation: 61467

SELECT categoryID, COUNT(articleID) AS numArticles 
FROM articles GROUP BY categoryID 
HAVING COUNT(articleID) > 3

Upvotes: 4

Oded
Oded

Reputation: 499112

Use a HAVING clause:

SELECT categoryID, COUNT(articleID) AS numArticles 
FROM articles 
 GROUP BY categoryID 
 HAVING COUNT(articleID) > 3

From MSDN:

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Upvotes: 2

pmariano
pmariano

Reputation: 166

For this don't use WHERE, you have to use HAVING instead.

Try this: 

SELECT categoryID, COUNT(articleID) AS numArticles 
FROM articles GROUP BY categoryID HAVING numArticles > 3

Upvotes: 0

codingbadger
codingbadger

Reputation: 44014

You need to use the Having clause

Select CategoryId, Count(ArticleId) as numArticles
From dbo.Articles
Group By CategoryId
Having Count(ArticleId) > 3

Upvotes: 2

Shurdoof
Shurdoof

Reputation: 1719

Use having

SELECT categoryID, COUNT(articleID) AS numArticles 
FROM articles GROUP BY categoryID HAVING COUNT(articleID) > 3

Upvotes: 6

Thyamine
Thyamine

Reputation: 1258

Use HAVING instead.

http://techonthenet.com/sql/having.php

Upvotes: 1

Related Questions