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
HAVING. So far as I recall,
HAVING can be used to use conditions publish-aggregation 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
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!