Jak policzyć dla różnych kolumn w tym samym stole

Tabela nr 01 Status:

StatusID    Status
-----------------------
 1          Opened
 2          Closed
 3          ReOpened
 4          Pending

Tabela nr 02 Claims:

ClaimID     CompanyName StatusID
--------------------------------------
1               ABC     1
2               ABC     1
3               ABC     2
4               ABC     4
5               XYZ     1
6               XYZ     1

Spodziewany wynik:

CompanyName TotalOpenClaims TotalClosedClaims TotalReOpenedClaims TotalPendingClaims
--------------------------------------------------------------------------------
ABC                 2           1                      0               1
XYZ                 2           0                      0               0

Jak napisać zapytanie, aby uzyskać oczekiwany wynik?

Jest to najłatwiejsze SUM()i CASEoświadczenie:

select CompanyName, 
sum(case when StatusID=1 then 1 else 0 end) as TotalOpenClaims,
sum(case when StatusID=2 then 1 else 0 end) as TotalClosedClaims,
sum(case when StatusID=3 then 1 else 0 end) as TotalReOpenedClaims,
sum(case when StatusID=4 then 1 else 0 end) as TotalPendingClaims
from Claims
group by CompanyName;

Jest to typowa transformacja przestawna, a agregacja warunkowa, jak sugeruje Phil , jest dobrym, starym sposobem jej implementacji.

Istnieje również bardziej nowoczesna składnia osiągania tego samego wyniku, która wykorzystuje klauzulę PIVOT:

SELECT
  CompanyName,
  TotalOpenClaims     = [1],
  TotalClosedClaims   = [2],
  TotalReOpenedClaims = [3],
  TotalPendingClaims  = [4]
FROM
  dbo.Claims
  PIVOT
  (
    COUNT(ClaimID)
    FOR StatusID IN ([1], [2], [3], [4])
  ) AS p
;

Ta wewnętrznie prawdopodobnie znacznie prostsza składnia jest równoważna zapytaniu GROUP BY Phila. Dokładniej, jest to odpowiednik tej odmiany:

SELECT
  CompanyName,
  TotalOpenClaims     = COUNT(CASE WHEN StatusID = 1 THEN ClaimID END),
  TotalClosedClaims   = COUNT(CASE WHEN StatusID = 2 THEN ClaimID END),
  TotalReOpenedClaims = COUNT(CASE WHEN StatusID = 3 THEN ClaimID END),
  TotalPendingClaims  = COUNT(CASE WHEN StatusID = 4 THEN ClaimID END)
FROM
  dbo.Claims
GROUP BY
  CompanyName
;

Tak więc zapytanie PIVOT jest zasadniczo niejawnym zapytaniem GROUP BY.

Jednak zapytania PIVOT są znacznie trudniejsze w obsłudze niż jawne zapytania GROUP BY z agregacją warunkową. Korzystając z PIVOT, należy zawsze pamiętać o jednej rzeczy:

• Wszystkie kolumny przestawionego zestawu danych ( Claimsw tym przypadku), które nie są wyraźnie wymienione w klauzuli PIVOT, są kolumnami GROUP BY .

Jeśli Claimsskłada się tylko z trzech kolumn pokazanych w twoim przykładzie, powyższe zapytanie PIVOT będzie działać zgodnie z oczekiwaniami, ponieważ najwyraźniej CompanyNamejest to jedyna kolumna, która nie została wyraźnie wymieniona w PIVOT, a zatem kończy się jako jedyne kryterium niejawnej funkcji GROUP BY.

Jeśli jednak Claimsma inne kolumny (powiedzmy ClaimDate), zostaną domyślnie użyte jako dodatkowe kolumny GROUP BY - to znaczy, że zapytanie będzie w zasadzie

GROUP BY CompanyName, ClaimDate, ... /* whatever other columns there are*/`

Wynik najprawdopodobniej nie będzie taki, jak chcesz.

Łatwo to jednak naprawić. Aby wykluczyć nieistotne kolumny z udziału w niejawnym grupowaniu, możesz po prostu użyć tabeli pochodnej, w której wybierzesz tylko kolumny potrzebne do wyniku, chociaż to sprawia, że ​​zapytanie jest mniej eleganckie:

SELECT
  CompanyName,
  TotalOpenClaims     = [1],
  TotalClosedClaims   = [2],
  TotalReOpenedClaims = [3],
  TotalPendingClaims  = [4]
FROM
  (SELECT ClaimID, CompanyName, StatusID FROM dbo.Claims) AS derived
  PIVOT
  (
    COUNT(ClaimID)
    FOR StatusID IN ([1], [2], [3], [4])
  ) AS p
;

Mimo to, jeśli Claimsjest już tabelą pochodną, ​​nie ma potrzeby dodawania kolejnego poziomu zagnieżdżania, po prostu upewnij się, że w bieżącej tabeli pochodnej wybierasz tylko kolumny wymagane do wygenerowania wyniku.

Możesz przeczytać więcej o PIVOT w instrukcji:

• Korzystanie z PIVOT i UNPIVOT

Wprawdzie moje doświadczenie dotyczy głównie MySQL i nie spędziłem dużo czasu na SQL Server. Byłbym bardzo zaskoczony, gdyby następujące zapytanie nie działało:

SELECT 
  CompanyName, 
  status, 
  COUNT(status) AS 'Total Claims' 
FROM Claim AS c 
  JOIN Status AS s ON c.statusId = s.statusId 
GROUP BY 
  CompanyName, 
  status;

Nie daje to danych wyjściowych w żądanym formacie, ale zapewnia wszystkie potrzebne informacje, aczkolwiek pomijając przypadki zerowe. Wydaje mi się to o wiele prostsze niż radzenie sobie z instrukcjami CASE wewnątrz zapytania, które wydaje się szczególnie złym pomysłem, jeśli jest ono po prostu używane do formatowania.