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.