Wie kann ich mehrere Zählungen mit einer SQL-Abfrage erhalten?

Question

Ich frage mich, wie ich diese Anfrage schreiben soll.

Ich weiß, dass diese eigentliche Syntax falsch ist, aber sie wird Ihnen helfen zu verstehen, was ich will.

Ich brauche sie in diesem Format, weil sie Teil einer viel größeren Abfrage ist.

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

Ich brauche dies alles in einer Abfrage.

Außerdem muss es in einer Zeile stehen, so dass das Folgende nicht funktionieren wird:

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

Answer 1

Sie können eine CASE Anweisung mit einer Aggregatfunktion. Dies ist im Grunde das Gleiche wie eine PIVOT Funktion in einigen RDBMS:

SELECT distributor_id,
    count(*) AS total,
    sum(case when level = 'exec' then 1 else 0 end) AS ExecCount,
    sum(case when level = 'personal' then 1 else 0 end) AS PersonalCount
FROM yourtable
GROUP BY distributor_id

Answer 2

Ein Weg, der mit Sicherheit funktioniert

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 ;

EDIT :
Siehe @KevinBalmforths Aufschlüsselung der Leistung, warum Sie diese Methode wahrscheinlich nicht verwenden wollen und stattdessen die Antwort von @Taryn wählen sollten. Ich lasse dies hier, damit die Leute ihre Optionen verstehen können.

Answer 3

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

COUNT zählt nur non null Werte und die DECODE gibt einen Nicht-Null-Wert zurück 1 nur, wenn Ihre Bedingung erfüllt ist.

Answer 4

Aufbauend auf anderen Antworten.

In beiden Fällen werden die richtigen Werte ermittelt:

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 ; 

Die Leistung ist jedoch recht unterschiedlich, was natürlich mit zunehmender Datenmenge an Bedeutung gewinnen wird.

Ich habe festgestellt, dass die Abfrage mit den SUMmen eine einzelne Tabellendurchsuchung durchführt, während die Abfrage mit den ZÄHLEN mehrere Tabellendurchsuchungen durchführt, vorausgesetzt, dass keine Indizes für die Tabelle definiert wurden.

Führen Sie zum Beispiel das folgende Skript aus:

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)

Markieren Sie die 2 SELECT-Anweisungen und klicken Sie auf das Symbol Geschätzten Ausführungsplan anzeigen. Sie werden sehen, dass die erste Anweisung eine Tabellendurchsuchung und die zweite 4 durchführen wird. Offensichtlich ist eine Tabellendurchsuchung besser als 4.

Das Hinzufügen eines geclusterten Index ist ebenfalls interessant. z.B..

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

Der erste SELECT oben führt einen einzelnen Clustered Index Scan durch. Der zweite SELECT führt 4 Clustered Index Seeks durch, die aber immer noch teurer sind als ein einzelner Clustered Index Scan. Ich habe das Gleiche an einer Tabelle mit 8 Millionen Zeilen ausprobiert, und der zweite SELECT war immer noch viel teurer.

Answer 5

Für MySQL kann dies abgekürzt werden zu:

SELECT distributor_id,
    COUNT(*) total,
    SUM(level = 'exec') ExecCount,
    SUM(level = 'personal') PersonalCount
FROM yourtable
GROUP BY distributor_id