How to count distinct values that all satisfy a condition in MySQL?

Question

I'm trying to write a query to find distinct values in a particular field, count the number of occurrences of that value where for all instances of that particular value another column value is satisfied, and then display the results as follows (more explanation to follow):

Example db:

RowId    Status       MemberIdentifier
-----    ------       ----------------
1       In Progress   111111111
2       Complete      123456789
3       Not Started   146782452
4       Complete      111111111
5       Complete      123456789
6       Not Started   146782452
7       Complete      111111111

Desired Result:

Status         MemberIdentifierCount 
------         ---------------------- 
Not Started    1
In Progress    1
Complete       1

In the above query, the number of distinct MemberIdentifiers with a given Status are counted and displayed. If a MemberIdentifier has two rows with Status 'Complete' but one with Status 'In Progress,' it is grouped and counted as in progress (i.e., MemberIdentifier= 111111111). For a MemberIdentifier to be grouped and counted as complete, all of its rows must have a Status of 'Complete' (i.e., MemberIdentifier= 123456789). Any insight would be appreciated (MySQL newbie).

Answer 1

Per MemberIdentifier find the status you consider appropriate, e.g. 'In Progress' wins over 'Complete' and 'Not Started'. 'Not Started' wins over 'Complete'. Use conditional aggregation for this.

select status, count(*)
from
(
  select 
    case when sum(status = 'In Progress') > 0 then 'In Progress'
         when sum(status = 'Not Started') > 0 then 'Not Started'
         else 'Complete'
    end as status
  from mytable
  group by memberidentifier
) statuses
group by status;

Answer 2

SELECT max_status AS Status
     , COUNT(*) AS ct
    FROM (
        SELECT MAX(Status) AS max_status
            FROM tbl
            GROUP BY MemberIdentifier
         ) AS a
    GROUP BY max_status;

This takes advantage of how these strings compare: "In Progress" > "Complete". In doing so, it does random things to any other member with multiple Statuses.

Answer 3

SQL

SELECT AdjustedStatus AS Status,
       COUNT(*) AS MemberIdentifierCount
FROM
(SELECT IF(Status='Complete',
           IF(EXISTS(SELECT Status
                     FROM tbl t2
                     WHERE t2.Status = 'In Progress'
                       AND t2.MemberIdentifier = t1.MemberIdentifier),
              'In Progress',
              'Complete'),
           Status) AS AdjustedStatus,
        MemberIdentifier
 FROM tbl t1
 GROUP BY AdjustedStatus, MemberIdentifier) subq
GROUP BY AdjustedStatus;

Online demo

http://rextester.com/FFGM6300

Explanation

The first IF() function checks whether the status is "Complete" and if so, checks for the existence of another record with the same MemberIdentifier but with a status of "In Progress": This is done via IF(EXISTS(SELECT...))). If found, a status of "In Progress" is assigned to the AdjustedStatus field, otherwise the AdjustedStatus is set from the (unadjusted) Status value.

With the adjusted status having been derived like this for each of the rows in the table, GROUP BY the AdjustedStatus and MemberIdentifier in order to get all unique combinations of these two field values. This is then made into a subquery - aliased as subq. Then aggregate over (GROUP BY) the AdjustedStatus and count the number of occurrences, i.e. the number of unique MemberIdentifiers for each.

Answer 4

I assume you have 2 tables as below

CREATE TABLE table1 (RowId INT PRIMARY KEY, MemberIdentifier VARCHAR(255));
INSERT INTO table1 (RowId, MemberIdentifier)
VALUES
(1,'111111111'), (2, '123456789'), (3, '146782452'), (4, '111111111'),(5,'123456789'), (6,'146782452'), (7,'111111111');


CREATE TABLE table2 (RowId INT PRIMARY KEY, Status VARCHAR(255));
INSERT INTO table2 (RowId, Status)
VALUES
(1,'In Progress'), (2,'Complete'   ), (3,'Not Started'), (4,'Complete'   ), (5,'Complete'   ), (6,'Not Started'), (7,'Complete'   );

Assuming you don't have millions of records in these tables, you can use the query below to achieve what you want.

SELECT CASE WHEN not_started.Status = 'Not Started' 
            THEN 'Not Started' 
            WHEN in_progress.Status = 'In Progress' 
            THEN 'In Progress' 
            WHEN complete.Status = 'Complete' 
            THEN 'Complete' 
       END AS over_all_status,
       COUNT(*) AS MemberIdentifierCount
  FROM  (SELECT DISTINCT t1.MemberIdentifier
          FROM table1 t1) main
        LEFT OUTER JOIN   
            (SELECT DISTINCT t1.MemberIdentifier, t2.Status
              FROM table1 t1,
                   table2 t2 
             WHERE t1.RowId = t2.RowId
               AND t2.Status = 'In Progress') in_progress
            ON (main.MemberIdentifier = in_progress.MemberIdentifier)
        LEFT OUTER JOIN
            (SELECT DISTINCT t1.MemberIdentifier, t2.Status
              FROM table1 t1,
                   table2 t2 
             WHERE t1.RowId = t2.RowId
               AND t2.Status = 'Not Started') not_started
        ON (main.MemberIdentifier = not_started.MemberIdentifier)
        LEFT OUTER JOIN
            (SELECT DISTINCT t1.MemberIdentifier, t2.Status
              FROM table1 t1,
                   table2 t2 
             WHERE t1.RowId = t2.RowId
               AND t2.Status = 'Complete') complete
        ON (main.MemberIdentifier = complete.MemberIdentifier)
GROUP BY over_all_status;

Basically the query creates one record per MemberIdentifier containing all three statuses possible. It then groups the result based on the overall status and outputs the count.

Output from the query is

Answer 5

use the following code to get the status of MemberIdentifier

select MemberIdentifier
,case 
when total = cn then 'Complete' 
when total < cn then 'In Progress' 
when total is null then 'Not Started' END as Fstatus
 from 
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
     where t1.MemberIdentifier = C.MemberIdentifier
     group by MemberIdentifier) as cn
from (
select MemberIdentifier,case status when 'In Progress' then -1 
                                    when 'Complete' Then 1 
                                    when 'Not Started' then null End as Stat from tbldata 
 ) C
 group by MemberIdentifier

 ) as f1

use the following code to get the count of MemberIdentifiers in particular status.

Select count(fstatus) counts,fstatus from (
select MemberIdentifier
,case when total = cn then 'Complete' 
      when total < cn then 'In Progress' 
      when total is null then 'Not Started' END as Fstatus
 from 
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
     where t1.MemberIdentifier = C.MemberIdentifier
     group by MemberIdentifier) as cn
from (
select MemberIdentifier
,case status when 'In Progress' then -1 when 'Complete' Then 1 when 'Not Started' then null End as Stat from tbldata 
 ) C
 group by MemberIdentifier

 ) as f1

 ) f2 group by fstatus

output :

counts  fstatus
1       Complete
1       In Progress
1       Not Started

Answer 6

If the order of precedence for status is

 Not Started
 In Progress
 Complete

We can use a shortcut...

   SELECT t.memberIdentifier
        , MAX(t.status) AS status
     FROM mytable t
    GROUP BY t.MemberIdentifier

That gets us the distinct memberIdentifier.

If there are any rows for a member that has rows in 'In Progress' and 'Complete' status, the query will return 'In Progress' as the status.

We will get status 'Complete' returned for a member only if that member does not have any rows with a status greater than 'Complete'.

To get counts from that result, we can reference that query as an inline view:

 SELECT q.status
      , COUNT(q.memberIdentifier) 
   FROM ( 
          SELECT t.memberIdentifier
               , MAX(t.status) AS status
            FROM mytable t
           GROUP BY t.MemberIdentifier
        ) q
  ORDER BY q.status

Think of if this way... MySQL runs the query between the parens first (MySQL calls this a "derived table". The results from the query is a set of rows which can be queried like a table.

We could do a COUNT(DISTINCT q.memberIdentifier) or, assuming memberIdentifier is guaranteed to be non-NULL, we could do COUNT(1) or SUM(1) and get an equivalent result. (The GROUP BY in the inline view guarantees us that memberIdentifier will be unique.)

---

In the more general case, where we don't have a convenient shortcut of alphabetic ordering for the precedence of the status... we could use an expression that returns values that are "in order". That makes the query a bit more complicated, but it would work the same.

We could replace t.status with something like this:

  CASE t.status
  WHEN 'Complete'    THEN 1
  WHEN 'In Progress' THEN 2
  WHEN 'Not Started' THEN 3
  ELSE 4
  END AS `status_priority`

And replace q.status with something the inverse, to convert back to strings:

  CASE q.status_priority
  WHEN 1 THEN 'Complete'
  WHEN 2 THEN 'In Progress'
  WHEN 3 THEN 'Not Started'
  ELSE NULL
  END AS `status`

We'd need to decide how we would handle values of status that aren't one of the three... are those going to be ignored, handled as a higher or lower priority than any of the others. (A test case would be rows with status = 'Unknown' and rows with status = 'Abracadabra.

Answer 7

I just modified @thorsten-kettner's solution as you were facing problem while joining table. I have assumed you 2 tables, table1 - which has at least 2 rows (RowID and MemberIdentifier ) and table2 - which has at least 2 rows ( RowID and Status)

select Status, count(*)
from(
  select 
    case when sum(newTable.Status = 'In Progress') > 0 then 'In Progress'
         when sum(newTable.Status = 'Not Started') > 0 then 'Not Started'
         else 'Complete'
    end as status
  from (
    select table1.RowId as RowId, table1.MemberIdentifier as MemberIdentifier, table2.Status as Status from table1 INNER JOIN table2 ON table1.RowId = table2.RowId
  )newTable
  group by newTable.MemberIdentifier
) statuses
group by Status;

Answer 8

Another way using a specific table to configure the order (map to Power of two integer).

This mapping allow bit_or aggregate to simply transpose data.

http://rextester.com/edit/ZSG98543

-- Table bit_progression to determine priority

CREATE TABLE bit_progression (bit_status int PRIMARY KEY, Status VARCHAR(255));
INSERT INTO bit_progression (bit_status, Status)
VALUES
(1,       'Not Started'),  
(2,       'Complete'   ),      
(4,       'In Progress');

select
    Status,
    count(*)
from
    (
    select
         MemberIdentifier,max(bit_status) bit_status
    from
        tbl natural join bit_progression
    group by
        MemberIdentifier
    ) Maxi natural join bit_progression
group by
    Status
;

produce

Status  count(*)

1   Complete    1
2   In Progress 1
3   Not Started 1

Extra :

select
    MemberIdentifier,
    bit_or(bit_status) bits_status,
    case when bit_or(bit_status) & 4 = 4 then true end as withStatusInProgress,
    case when bit_or(bit_status) & 2 = 2 then true end as withStatusComplete,
    case when bit_or(bit_status) & 1 = 1 then true end as withStatusNotStarted
from
    tbl natural join bit_progression
group by
    MemberIdentifier
;

produce it :

MemberIdentifier bits_status    withStatusInProgress    withStatusComplete  withStatusNotStarted

111111111   6   1       1       NULL
123456789   2   NULL    1       NULL
146782452   1   NULL    NULL    1