tiene - ¿Cómo contar valores distintos que cumplen una condición en MySQL?

sql contar registros condicion

Question

Intento escribir una consulta para encontrar valores distintos en un campo particular, contar el número de ocurrencias de ese valor donde para todas las instancias de ese valor particular se cumple otro valor de columna, y luego mostrar los resultados de la siguiente manera (más explicación para seguir):

Ejemplo 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

Resultado deseado:

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

En la consulta anterior, se cuenta y se muestra el número de MemberIdentifiers distintos con un estado determinado. Si un MemberIdentifier tiene dos filas con el estado ''Completo'' pero uno con el estado ''En curso'', se agrupa y cuenta como en progreso (es decir, MemberIdentifier = 111111111). Para que MemberIdentifier se agrupe y cuente como completo, todas sus filas deben tener un estado de ''Completo'' (es decir, MemberIdentifier = 123456789). Cualquier idea sería apreciada (newbie MySQL).

Answer 1

Otra forma de usar una tabla específica para configurar el orden (mapa a Potencia de dos enteros).

Esta asignación permite que bit_or agregado simplemente transponga datos.

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
;

producirlo:

MemberIdentifier bits_status    withStatusInProgress    withStatusComplete  withStatusNotStarted

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

Answer 2

Acabo de modificar la solución de @ thorsten-kettner porque estaba enfrentando un problema al unirse a la mesa. He asumido 2 tablas, tabla1 - que tiene al menos 2 filas (RowID y MemberIdentifier) ​​y table2 - que tiene al menos 2 filas (RowID y estado)

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 3

Per MemberIdentifier encuentra el estado que considere apropiado; por ejemplo, ''In Progress'' gana a ''Complete'' y ''Not Started'' . ''Not Started'' gana por ''Complete'' . Use la agregación condicional para esto.

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 4

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;

Demo en línea

http://rextester.com/FFGM6300

Explicación

La primera función IF() comprueba si el estado es "Completo" y, de ser así, comprueba la existencia de otro registro con el mismo MemberIdentifier pero con un estado de "En curso": Esto se hace a través de IF(EXISTS(SELECT...))) . Si se encuentra, se asigna un estado de "En curso" al campo Estado AdjustedStatus , de lo contrario, el Estado AdjustedStatus se establece a partir del valor de Status (sin ajustar).

Con el estado ajustado se ha derivado así para cada una de las filas de la tabla, GROUP BY el estado AdjustedStatus y MemberIdentifier para obtener todas las combinaciones únicas de estos dos valores de campo. Esto se convierte en una subconsulta - alias como subq . Luego agregue ( GROUP BY ) el Estado AdjustedStatus y cuente el número de ocurrencias, es decir, el número de MemberIdentifier únicos para cada uno.

Answer 5

Si el orden de precedencia para el status es

 Not Started
 In Progress
 Complete

Podemos usar un atajo ...

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

Eso nos da el memberIdentifier distintivo.

Si hay filas para un miembro que tiene filas en estado ''In Progress'' y ''Complete'' , la consulta devolverá ''In Progress'' como el estado.

Obtendremos el estado ''Complete'' devuelto por un miembro solo si ese miembro no tiene ninguna fila con un estado mayor que ''Complete'' .

Para obtener recuentos de ese resultado, podemos hacer referencia a esa consulta como una vista en línea:

 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

Piense de esta manera ... MySQL ejecuta primero la consulta entre los parens (MySQL llama a esto una "tabla derivada". Los resultados de la consulta son un conjunto de filas que se pueden consultar como una tabla.

Podríamos hacer un COUNT(DISTINCT q.memberIdentifier) o, suponiendo que memberIdentifier garantice que no es NULL, podríamos hacer COUNT(1) o SUM(1) y obtener un resultado equivalente. (El GROUP BY en la vista en línea nos garantiza que memberIdentifier será único).

En el caso más general, donde no tenemos un atajo conveniente de ordenamiento alfabético para la precedencia del estado ... podríamos usar una expresión que arroje valores que están "en orden". Eso hace que la consulta sea un poco más complicada, pero funcionaría igual.

Podríamos reemplazar t.status con algo como esto:

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

Y reemplace q.status con algo inverso, para convertir de nuevo a cadenas:

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

Tendríamos que decidir cómo manejaríamos los valores de estado que no son uno de los tres ... serán ignorados, manejados como una prioridad más alta o más baja que cualquiera de los otros. (Un caso de prueba serían las filas con el status = ''Unknown'' y las filas con el status = ''Abracadabra .

Answer 6

Supongo que tienes 2 tablas como las siguientes

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''   );

Suponiendo que no tiene millones de registros en estas tablas, puede usar la consulta a continuación para lograr lo que desea.

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;

Básicamente, la consulta crea un registro por MemberIdentifier que contiene los tres estados posibles. A continuación, agrupa el resultado según el estado general y genera el recuento.

El resultado de la consulta es

Answer 7

use el siguiente código para obtener el estado de 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 el siguiente código para obtener el conteo de MemberIdentifiers en un estado particular.

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 salida:

counts  fstatus
1       Complete
1       In Progress
1       Not Started

Answer 8

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;

Esto aprovecha la forma en que se comparan estas cadenas: "En curso"> "Completar". Al hacerlo, hace cosas al azar a cualquier otro miembro con múltiples estados.