Como obter contagem para diferentes colunas na mesma tabela

É mais fácil com SUM()uma CASEdeclaração:

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;

Essa é uma transformação típica de pivô, e a agregação condicional, como sugerido por Phil , é a boa e velha maneira de implementá-la.

Há também uma sintaxe mais moderna para alcançar o mesmo resultado, que usa a cláusula 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
;

Internamente, essa sintaxe aparentemente mais simples é equivalente à consulta GROUP BY de Phil. Mais exatamente, é equivalente a esta variação:

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
;

Portanto, uma consulta PIVOT é uma consulta GROUP BY implícita, essencialmente.

As consultas PIVOT, no entanto, são notoriamente mais complicadas de lidar do que as consultas GROUP BY explícitas com agregação condicional. Ao usar o PIVOT, você precisa sempre ter em mente uma coisa:

• Todas as colunas do conjunto de dados que está sendo dinamizado ( Claimsneste caso) que não são explicitamente mencionadas na cláusula PIVOT são colunas GROUP BY .

Se Claimsconsistir apenas nas três colunas mostradas no seu exemplo, a consulta PIVOT acima funcionará conforme o esperado, pois aparentemente CompanyNameé a única coluna não mencionada explicitamente no PIVOT e, portanto, acaba sendo o único critério do GROUP BY implícito.

No entanto, se Claimstiver outras colunas (digamos, ClaimDate), elas serão usadas implicitamente como colunas GROUP BY adicionais – ou seja, sua consulta estará essencialmente fazendo

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

O resultado provavelmente não será o que você deseja.

Isso é fácil de corrigir, no entanto. Para excluir colunas irrelevantes da participação no agrupamento implícito, basta usar uma tabela derivada, onde você selecionará apenas as colunas necessárias para o resultado, embora isso torne a consulta menos elegante:

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
;

Ainda assim, se Claimsjá for uma tabela derivada, não há necessidade de adicionar outro nível de aninhamento, apenas certifique-se de que na tabela derivada atual você esteja selecionando apenas as colunas necessárias para produzir a saída.

Você pode ler mais sobre PIVOT no manual:

• Usando PIVOT e UNPIVOT

É certo que minha experiência é principalmente com MySQL e não passei muito tempo no SQL Server. Eu ficaria muito surpreso se a seguinte consulta não funcionasse:

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;

Isso não fornece a saída no formato desejado, mas fornece todas as informações desejadas, embora deixando de fora os zeros. Isso parece muito mais simples para mim do que lidar com instruções CASE dentro de uma consulta que parece uma ideia especialmente ruim se estiver sendo usada apenas para formatação.