I am looking to get all of the user id's (uid) which have published a lot 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 a bit of reading through, I am speculating my mistake is by using my use of COUNT, however i can't discover what I am doing wrong. Any hints? Thanks heaps!

A lot more like...

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

EDIT: I'll add a little more information on WHERE versus. HAVING. So far as I recall, HAVING can be used to use conditions publish-aggregation by GROUP BY. WHERE can be used to use conditions towards the table generally.

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

Why this does not work (re: I can not discover what I am doing wrong):

  • n.type = 'article' << this returns a BOOLEAN, that is either true/false or 1/
  • COUNT ( x ) boosts the counter whenever x isn't NULL
  • both of just oneOr aren't null, therefore, it counts All sorts, and is the same as COUNT(*)

You desired (this answer was already given, but repeating here)

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

Generally you might write your COUNT correctly using Situation, however the WHERE filter is generally faster

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

COUNT() are only able to total arranged records or posts in arranged results it cannot be employed to count records that meet an ailment in the manner you would like. Try either subquerying the expression, or tugging up in to the choose list where you can use it 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= in MySQL (as Richard described), your query works (!) by altering only COUNT to SUM :

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

But that's not the best way to do that!