How to use MySQL's COUNT() correctly?

I'm trying to get a listing of user id's (uid) that have posted more than 3 articles. Here's my sql:

SELECT n.uid
FROM node
GROUP BY n.uid
HAVING COUNT( n.type = 'article' ) > 3

After doing some reading, I'm guessing my mistake is with my usage of COUN开发者_StackOverflowT, but I can't find out what I'm doing wrong. Any hints? Thanks heaps!

More like...

SELECT uid
FROM node
WHERE type = 'article'
GROUP BY uid
HAVING COUNT(uid) > 3

EDIT: I'll add a bit more info on WHERE vs. HAVING. As far as I remember, HAVING is used to apply conditions post-aggregation by GROUP BY. WHERE is used to apply conditions to the table in general.

HAVING COUNT( n.type = 'article' ) > 3

Why this doesn't work _{(re: I can't find out what I'm doing wrong)}:

• n.type = 'article' << this returns a BOOLEAN, which is either true/false or 1/0
• COUNT ( x ) increases the counter whenever x is NOT NULL
• both of 1/0 are not null, therefore, it counts ALL types, and is equivalent to COUNT(*)

You wanted (this answer has already been given, but repeating here)

SELECT n.uid
FROM node
WHERE n.type = 'article'
GROUP BY n.uid
HAVING COUNT( * ) > 3

In general you could also write your COUNT properly using CASE, but the WHERE filter is normally faster

SELECT n.uid
FROM node
GROUP BY n.uid
HAVING COUNT( CASE WHEN n.type = 'article' THEN 1 END ) > 3

COUNT() can only count up grouped records or columns in grouped results; it can't be used to count records that meet a condition in the way you want. Try either subquerying the expression, or pulling it up into the select list where it can be used in conditions:

SELECT n.uid, COUNT(*)
FROM node n
WHERE n.type = 'article'
GROUP BY n.uid
HAVING COUNT(*) > 3

SELECT n.uid
FROM node n
WHERE (SELECT COUNT(*) from node n2 where n2.uid = n.uid AND n.type == 'article')  > 3

SELECT n.id, COUNT(n.id) as my_count
FROM node
WHERE n.type = 'article'
GROUP BY n.id
HAVING my_count > 3

SELECT n.uid
FROM node n
--- keep only articles postings
WHERE n.type = 'article'
--- group by user
GROUP BY n.uid
--- count rows while grouping (you can use COUNT(*) instead, result is same)
HAVING COUNT( n.uid ) > 3

Because TRUE=1 and FALSE=0 in MySQL (as Richard explained), your query would work (!) by changing only COUNT to SUM :

SELECT n.uid
FROM node n
GROUP BY n.uid
HAVING SUM( n.type = 'article' ) > 3

But that is not the proper way to do this!