How to get count for different columns on same table

Question

Table #01 Status:

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

Table #02 Claims:

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

Expected Result:

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

How do I need to write the query so that I could get the result as expected?

Answer 1

It's easiest with SUM() and a CASE statement:

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;

Answer 2

This is a typical pivot transformation, and conditional aggregation, as suggested by Phil, is the good old way of implementing it.

There is also a more modern syntax of achieving the same result, which uses the PIVOT clause:

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
;

Internally this arguably simpler looking syntax is equivalent to Phil's GROUP BY query. More exactly, it is equivalent to this variation:

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
;

So, a PIVOT query is an implicit GROUP BY query, essentially.

PIVOT queries, however, are notoriously trickier in handling than explicit GROUP BY queries with conditional aggregation. When you are using PIVOT, you need to always keep in mind this one thing:

• All columns of the dataset being pivoted (Claims in this case) that are not explicitly mentioned in the PIVOT clause are GROUP BY columns.

If Claims consists of only the three columns shown in your example, the PIVOT query above will work as expected, because apparently CompanyName is the only column not explicitly mentioned in PIVOT and thus ends up as the only criterion of the implicit GROUP BY.

However, if Claims has other columns (say, ClaimDate), they will implicitly be used as additional GROUP BY columns – that is, your query will essentially be doing

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

The result will most likely be not what you want.

That is easy to fix, though. In order to exclude irrelevant columns from participating in the implicit grouping, you can just use a derived table, where you will select only the columns needed for the result, although that makes the query less elegant-looking:

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
;

Still, if Claims is already a derived table, there is no need to add another level of nesting, just make sure that in the current derived table you are selecting only the columns required to produce the output.

You can read more about PIVOT in the manual:

• Using PIVOT and UNPIVOT

Answer 3

Admittedly my experience is with MySQL mostly and I haven't spent much time on SQL Server. I would be very surprised if the following query didn't work:

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;

This doesn't give you the output in the format that you want but it does give you all of the information you want, albeit leaving out the zero cases. This feels much simpler to me than dealing with CASE statements inside of a query which feel like an especially bad idea if it is just being used for formatting.