New-To-SQL New-To-SQL - 1 month ago 6
MySQL Question

Mysql counting row with specific attributes

So, here is my table:

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



  1. How can I select then count all people having shopped at A AND B, then

  2. A AND B ONLY?

  3. A OR B BUT NO OTHER?



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

Answer

Names of people shopping at 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 AND   -- B is present

Names of people having at A or B (but no other):

SELECT name
FROM yourTable
WHERE Store IN ('A', 'B')           -- A or B are present
GROUP BY name
HAVING COUNT(DISTINCT Store) <= 2   -- at most two unique stores, implying
                                    -- only A or B present

Names of people who shopped only at 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

Demo for second query here:

SQLFiddle

Comments