SQL select with multiple counts from the same column

Question

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

Mureinik · Accepted Answer · 2017-09-29 13:30:28Z

1

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

answered Sep 29, 2017 at 13:30

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL select with multiple counts from the same column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related