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?

Answer 1

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

Answer 2

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