Mysql count string with specific attributes

Question

So here is my table:

Id  | name | Store   
001 | John | A   
001 | John | A     
001 | John | A       
001 | John | B
001 | John | B 
001 | John | D
002 | Bob  | B 
002 | Bob  | C 
003 | Dave | C 
004 | Pamela | A
004 | Pamela | B
004 | Pamela | C 
005 | Nick   | D
005 | Nick   | D
005 | Nick   | D

• How can I choose to count all the people who made a purchase in AND B, then
• A and B ONLY?
• A OR B, BUT NO OTHER?
• D ONLY?

In my example, the expected result: (1) John + Pamela, (2) John, (3) John (4) Nick

Answer 1

Names of people shopping in A and B (and possibly elsewhere):

SELECT name
FROM yourTable
GROUP BY name
HAVING SUM(CASE WHEN Store = 'A' THEN 1 END) > 0 AND   -- A is present
       SUM(CASE WHEN Store = 'B' THEN 1 END) > 0       -- B is present

Names of people with A or B (but not others):

SELECT name
FROM yourTable
GROUP BY name
HAVING (SUM(CASE WHEN Store = 'A' THEN 1 END) > 0 OR           -- A is present
        SUM(CASE WHEN Store = 'B' THEN 1 END) > 0) AND         -- B is present
       SUM(CASE WHEN Store NOT IN ('A', 'B') THEN 1 END) = 0   -- only A or B

Names of people who buy only on A and B:

SELECT name
FROM yourTable
GROUP BY name
HAVING SUM(CASE WHEN Store = 'A' THEN 1 END) > 0 AND   -- A is present
       SUM(CASE WHEN Store = 'B' THEN 1 END) > 0 AND   -- B is present
       COUNT(DISTINCT Store) = 2                       -- only A and B are present

Names of people who buy only at store D:

SELECT name
FROM yourTable
GROUP BY name
HAVING SUM(CASE WHEN Store <> 'D' THEN 1 END) = 0      -- only store D

The demo for the second request is here:

SQLFiddle

Answer 2

group by having:

select name
from t
group by name
having sum(store = 'A') > 0 and
       sum(store = 'B') > 0;

, . :

select name
from t
group by name
having sum(store = 'A') > 0 and
       sum(store = 'B') > 0 and
       sum(store not in ('A', 'B')) = 0;