R100 R100 - 3 years ago 194
SQL Question

SQL select with multiple counts from the same column

I have one table in SQL with the following columns: Status, CreateDateTime and Case Type

Example of 10 rows of data looks like the following:

Resolved-Withdrawn 2017-08-28 10:20:58.820 Termination Quote DE
Pending-Customer 2017-08-28 10:44:37.987 Termination Quote DE
Pending-Customer 2017-08-28 10:44:39.953 Termination Quote DE
Pending-Customer 2017-08-28 10:51:55.643 Termination Quote DE
Pending-Customer 2017-08-28 10:51:56.513 Termination Quote DE
Pending-Customer 2017-08-28 10:54:20.160 Termination Quote DE
Pending-Customer 2017-08-28 10:54:20.747 Termination Quote DE
Pending-Customer 2017-09-10 06:12:52.113 Termination Quote DE
Pending-Customer 2017-09-15 12:34:32.657 Termination Quote DE


I would like to achieve the following:

Status Total_August Total_September
-----------------------------------------------------
Resolved-Withdrawn 1 0
Pending-Customer 6 2


How would I create this query within SQL?

My initial idea would be something like this (which obviously doesn't work), but maybe it gives you a better understanding of my thought process as a non-experience user.

SELECT Status, COUNT(pxCreateDateTime) as Total_August, COUNT(pxCreateDateTime) as Total_September
FROM ContractMgtWork
WHERE MONTH(pxCreateDateTime) = 8 and YEAR(pxCreateDateTime) = 2017 AND
CaseType = 'Amortization Schedule DE'
GROUP BY Status

Answer Source

count, like many other aggregate functions, skips nulls. You can utilize this property by counting a case expression with whatever your want to count:

SELECT   Status, 
         COUNT(CASE WHEN MONTH(pxCreateDateTime) = 8 THEN 1 END) AS Total_August,
         COUNT(CASE WHEN MONTH(pxCreateDateTime) = 9 THEN 1 END) AS Total_September
FROM     ContractMgtWork
WHERE    YEAR(pxCreateDateTime) = 2017 AND 
         CaseType = 'Amortization Schedule DE'
GROUP BY Status 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download