mySQL count functionality

Question

I'm trying to count all records from my 'jobs' table that have a 'completed' status, excluding any jobs that are either 'fix-it' or 'vacancy' job types. This is what I'm using, but I'm not sure if this is giving me the correct results:

SELECT id, client_id, COUNT(*) AS count FROM jobs 
WHERE jobType != 'fix-it' AND jobType != 'vacancy' AND status = 'completed' 
GROUP by jobs.client_id

I then look at the result to see if I have two or more completed jobs under a given client.

Does this look correct?

Could you post a little example data? Your code looks OK, just wanted to test stuff. — TARehman
– TARehman, Commented Sep 10, 2012 at 20:26
If "jobType" is NULL, do you want to see those records? Why do you select "id" in the query? What do you think it is telling you? — pilcrow
– pilcrow, Commented Sep 10, 2012 at 20:53
Yes, i would want to see those records. Wouldn't they be included though, because I'm using != to filter? NULL would not equal either 'vacancy' or 'fix-it', and as such it would be included right? As far as the 'id' goes, you're right, I don't need it. — Jeremy
– Jeremy, Commented Sep 10, 2012 at 20:59
Re: NULL, no. NULL != col evaluates to NULL, not true or false. Re: "id", it's not just not needed, it doesn't make sense, and I'd recommend using ONLY_FULL_GROUP_BY. — pilcrow
– pilcrow, Commented Sep 10, 2012 at 21:12

Marc B · Accepted Answer · 2012-09-10 20:26:05Z

2

You could replace the two jobtype things with a single NOT IN clause, to make things a little more legible...

WHERE jobType NOT IN ('fix-it', 'vacancy') AND ...

otherwise it looks ok

answered Sep 10, 2012 at 20:26

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Grimshaw · Accepted Answer · 2012-09-10 20:25:32Z

0

This works in SQL Server, not sure about MySql:

SELECT id, client_id, COUNT(*) AS count FROM jobs 
WHERE jobType != 'fix-it' AND jobType != 'vacancy' AND status = 'completed' 
GROUP by jobs.client_id
HAVING Count(*) > 1

answered Sep 10, 2012 at 20:25

Paul Grimshaw

21.2k6 gold badges43 silver badges60 bronze badges

5 Comments

Marc B Over a year ago

it won't work. you'll get the "selecting non-aggregate fields not in a group by" error. SQL server's stupid like that.

Jeremy Over a year ago

I actually do that > 1 count check using PHP, so that's not a problem. I'm mainly concerned whether the above query would generate the intended results, given that I'm pretty new to using COUNT.

Paul Grimshaw Over a year ago

@Marc B's comment is very true, you'd have to add the id and client id to group by in SQL server

Paul Grimshaw Over a year ago

@Jeremy Unless you want to display all the results, I always try to filter in the query...

D'Arcy Rittich Over a year ago

@MarcB You have it backwards. It's MySQL that is stupid here, by returning an indeterminate id.

Collectives™ on Stack Overflow

mySQL count functionality

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related