CODEWITHSUNDEEP
mysqlsqlselectgroup-by
Jon Robinson
Jon Robinson

Reputation: 891

MySQL Count Unique Of One Column Where Other Column Has Single Value In Group By

Please see the table below. I would like to COUNT the distinct visitors (visitor_id), grouping by the group (group_id), - but only count those where the outcome has always been 'declined' for that visitor.

Something like

SELECT group_id, COUNT(DISTINCT visitor_id) AS always_declines
FROM customer_actions 
WHERE outcome='declined' [[AND HAS NEVER BEEN IN ('purchased')]]
GROUP BY group_id;

Here is a simplified version of my table:

SELECT * FROM customer_actions;
+----+------------+-----------+----------+
| id | visitor_id | outcome   | group_id |
+----+------------+-----------+----------+
|  1 |          5 | purchased |        1 |
|  2 |          5 | purchased |        1 |
|  3 |          6 | purchased |        1 |
|  4 |          7 | declined  |        1 |
|  5 |          6 | declined  |        1 |
|  6 |          7 | purchased |        1 |
|  7 |          8 | declined  |        1 |
|  8 |          8 | declined  |        1 |
+----+------------+-----------+----------+
8 rows in set (0.00 sec)

So basically if it worked the result I am looking for the first and only row (in this case) returned:

group_id = 1

always_declines = 1 (corresponding to visitor 8 who has only ever declined)

Upvotes: 1

Views: 121

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

One way to approach this is as two aggregations. First, aggregate by groups and visitors, to get the right visitors. Then count the rows that remain:

SELECT group_id, count(*) AS always_declines
FROM (SELECT group_id, visitor_id
      FROM customer_actions 
      GROUP BY group_id, visitor_id
      HAVING SUM(outcome <> 'declined') = 0
     ) gv
GROUP BY group_id;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311188

The not exists operator should do the trick:

SELECT   group_id, COUNT(DISTINCT visitor_id) AS always_declines
FROM     customer_actions  ca1
WHERE    NOT EXISTS (SELECT *
                     FROM   customer_actions ca2
                     WHERE  ca1.group_id = ca2.group_id AND
                            ca1.visitor_id = ca2.visitor_id AND
                            ca2.outcome != 'declined')
GROUP BY group_id;

Upvotes: 2

Related Questions