¿Cómo get múltiples conteos con una consulta SQL?

Me pregunto cómo escribir esta consulta.

Sé que esta syntax real es falsa, pero te ayudará a entender lo que quiero. Lo necesito en este formatting, porque es parte de una consulta mucho más grande.

SELECT distributor_id, COUNT(*) AS TOTAL, COUNT(*) WHERE level = 'exec', COUNT(*) WHERE level = 'personal' 

Necesito que todo esto se devuelva en una consulta.

Además, debe estar en una fila, por lo que lo siguiente no funcionará:

 'SELECT distributor_id, COUNT(*) GROUP BY distributor_id' 

Puede usar una instrucción CASE con una function agregada. Esto es básicamente lo mismo que una function PIVOT en algunos RDBMS:

 select distributor_id, count(*) total, sum(case when level = 'exec' then 1 else 0 end) ExecCount, sum(case when level = 'personal' then 1 else 0 end) PersonalCount from yourtable group by distributor_id 

Una forma que funciona con security

 SELECT a.distributor_id, (SELECT COUNT(*) FROM myTable WHERE level='personal' and distributor_id = a.distributor_id) as PersonalCount, (SELECT COUNT(*) FROM myTable WHERE level='exec' and distributor_id = a.distributor_id) as ExecCount, (SELECT COUNT(*) FROM myTable WHERE distributor_id = a.distributor_id) as TotalCount FROM (SELECT DISTINCT distributor_id FROM myTable) a ; 

EDITAR:
Consulte el desglose del performance de @ KevinBalmforth para saber por qué es probable que no desee utilizar este método y, en su lugar, opte por la respuesta de @ bluefeet. Me voy de esto para que la gente pueda entender sus opciones.

 SELECT distributor_id, COUNT(*) AS TOTAL, COUNT(IF(level='exec',1,null)), COUNT(IF(level='personal',1,null)) FROM sometable; 

COUNT solo count valores non null y el DECODE devolverá el valor nulo 1 solo si se cumple su condición.

Para mysql esto se puede acortar a

 select distributor_id, count(*) total, sum(level = 'exec') ExecCount, sum(level = 'personal') PersonalCount from yourtable group by distributor_id 

Basándose en otras respuestas publicadas.

Ambos producirán los valores correctos:

 select distributor_id, count(*) total, sum(case when level = 'exec' then 1 else 0 end) ExecCount, sum(case when level = 'personal' then 1 else 0 end) PersonalCount from yourtable group by distributor_id SELECT a.distributor_id, (SELECT COUNT(*) FROM myTable WHERE level='personal' and distributor_id = a.distributor_id) as PersonalCount, (SELECT COUNT(*) FROM myTable WHERE level='exec' and distributor_id = a.distributor_id) as ExecCount, (SELECT COUNT(*) FROM myTable WHERE distributor_id = a.distributor_id) as TotalCount FROM myTable a ; 

Sin embargo, el performance es bastante diferente, lo que obviamente será más relevante a medida que la cantidad de datos crezca.

Descubrí que, suponiendo que no se definieran índices en la tabla, la consulta que usa SUM podría hacer un escaneo de tabla única, mientras que la consulta con los COUNT haría múltiples escaneos de tabla.

Como ejemplo, ejecute el siguiente script:

 IF OBJECT_ID (N't1', N'U') IS NOT NULL drop table t1 create table t1 (f1 int) insert into t1 values (1) insert into t1 values (1) insert into t1 values (2) insert into t1 values (2) insert into t1 values (2) insert into t1 values (3) insert into t1 values (3) insert into t1 values (3) insert into t1 values (3) insert into t1 values (4) insert into t1 values (4) insert into t1 values (4) insert into t1 values (4) insert into t1 values (4) SELECT SUM(CASE WHEN f1 = 1 THEN 1 else 0 end), SUM(CASE WHEN f1 = 2 THEN 1 else 0 end), SUM(CASE WHEN f1 = 3 THEN 1 else 0 end), SUM(CASE WHEN f1 = 4 THEN 1 else 0 end) from t1 SELECT (select COUNT(*) from t1 where f1 = 1), (select COUNT(*) from t1 where f1 = 2), (select COUNT(*) from t1 where f1 = 3), (select COUNT(*) from t1 where f1 = 4) 

Resalte las 2 instrucciones SELECT y click el ícono Mostrar plan de ejecución estimado. Verá que la primera instrucción hará una exploración de tabla y la segunda lo hará 4. Obviamente, una exploración de tabla es mejor que 4.

Agregar un índice agrupado también es interesante. P.ej

 Create clustenetworking index t1f1 on t1(f1); Update Statistics t1; 

El primer SELECT de arriba hará un único escaneo de índice agrupado. El segundo SELECT hará 4 búsquedas de índices agrupados, pero aún son más caros que un solo análisis de índice agrupado. Intenté lo mismo en una table con 8 millones de filas y el segundo SELECT todavía era mucho más caro.

Bueno, si debe tenerlo todo en una consulta, podría hacer una unión:

 SELECT distributor_id, COUNT() FROM ... UNION SELECT COUNT() AS EXEC_COUNT FROM ... WHERE level = 'exec' UNION SELECT COUNT(*) AS PERSONAL_COUNT FROM ... WHERE level = 'personal'; 

O bien, si puede hacerlo después del procesamiento:

 SELECT distributor_id, COUNT(*) FROM ... GROUP BY level; 

Obtendrás el conteo de cada nivel y tendrás que sumrlos todos para get el total.

Hago algo como esto, donde le doy a cada tabla un nombre de cadena para identificarlo en la columna A y un conteo para la columna. Luego los uní a todos para que se apilen. El resultado es bastante bueno en mi opinión, no estoy seguro de lo eficiente que es en comparación con otras opciones, pero me consiguió lo que necesitaba.

 select 'table1', count (*) from table1 union select 'table2', count (*) from table2 union select 'table3', count (*) from table3 union select 'table4', count (*) from table4 union select 'table5', count (*) from table5 union select 'table6', count (*) from table6 union select 'table7', count (*) from table7; 

Resultado:

 ------------------- | String | Count | ------------------- | table1 | 123 | | table2 | 234 | | table3 | 345 | | table4 | 456 | | table5 | 567 | ------------------- 

Basado en la respuesta aceptada de Bluefeet con un matiz adicional usando OVER ()

 select distributor_id, count(*) total, sum(case when level = 'exec' then 1 else 0 end) OVER() ExecCount, sum(case when level = 'personal' then 1 else 0 end) OVER () PersonalCount from yourtable group by distributor_id 

Usar OVER() con nada en el () le dará la count total para todo el set de datos.