Mysql – Exclude entries from SELECT COUNT

MySQLmysql-5

I'm new to MySQL and I have a problem with one task.
I have a table like this:

+------------+------------+-------------+
| code       | name       | location    |
+------------+------------+-------------+
|          1 |          a |           x |
|          2 |          a |           x |
|          3 |          a |           x |
|          4 |          a |           x |
|          5 |          b |           x |
|          6 |          b |           x |
|          7 |          c |           x |
|          8 |          c |           x |
|          9 |          c |           x |
|         10 |          c |           x |
+------------+------------+-------------+

I want to count all the codes that have location X but exclude all if in at least one the location is set to Y.

So far I managed to do this:

SELECT COUNT( * ) AS  `how much` ,  `name` ,  `location` 
FROM  table
WHERE  `location` =  'x' AND `location` <> 'y'
GROUP BY   `name`
ORDER BY   `name`

which gave me

+------------+------------+-------------+
| how much   | name       | location    |
+------------+------------+-------------+
|          4 |          a |           x |
|          2 |          b |           x |
|          4 |          c |           x |
+------------+------------+-------------+

As soon as I change the location on code 1 from x to y
it changes to:

+------------+------------+-------------+
| how much   | name       | location    |
+------------+------------+-------------+
|          3 |          a |           x |
|          2 |          b |           x |
|          4 |          c |           x |
+------------+------------+-------------+

but I need to exclude it:

+------------+------------+-------------+
| how much   | name       | location    |
+------------+------------+-------------+
|          2 |          b |           x |
|          4 |          c |           x |
+------------+------------+-------------+

How do I do this?

Best Answer

This should do the trick.

SELECT COUNT( * ) AS  `how much` ,  `name` ,  `location` 
FROM  table
WHERE  name NOT IN (SELECT name FROM table WHERE location = 'y')
GROUP BY   `name`
ORDER BY   `name`

The idea behind this is : we are counting the number of name but we have to exclude rows of name where in ANOTHER row there is location = 'y'.

If you do WHERE location <> 'y', you just exclude the line including location = 'y' but the others rows with location = 'x' are still there and used in count(*).

So, the goal is to exclude ALL names where a minimum of one row has location = 'y'. This is why we do name NOT IN (...).

The part between the brackets select all name where location = 'y'. This outputs all names we want to exclude from our COUNT(*). We just have to exclude all these results from our query.

That's all folks !

Related Question