Group by a certain criteria - SQL

user_19549748 0
Viewed 54

I need to group my customers using a criteria specified in a column but using multiple rows as condition. As an example:

CustomerID Status Month
Customer1 Rejected June
Customer2 Accepted June
Customer2 Rejected June
Customer3 Rejected June
Customer4 Accepted June

In this case, I would like to group by month and count the amount of distinct customers that were only rejected, the amount that were only accepted and the amount that where both rejected and accepted- Therefore, the expected outcome would be:

Month accepted & rejected rejected accepted
June 1 2 1

Could someone help me?

distinct group-by sql
1 Answers

Check if this suits your needs:

CREATE TABLE CustomerStatus
(
CustomerID  INT, 
Status VARCHAR(20),
Month VARCHAR(10)
)

INSERT INTO CustomerStatus(CustomerID, Status, Month) VALUES
(1, 'Rejected', 'June'),
(2, 'Accepted', 'June'),
(2, 'Rejected', 'June'),
(3, 'Rejected', 'June'),
(4, 'Accepted', 'June')

SELECT CustomerStatus.[Month], 
       SUM(CASE 
                WHEN accepted is NOT null AND rejected is NOT null THEN 1
                ELSE 0
            END) [Accepted & Rejected],

       SUM(CASE 
                WHEN rejected is NOT null AND accepted is NULL THEN 1
                ELSE 0
            END) [Rejected],

        SUM(CASE 
                WHEN accepted is NOT null AND rejected is NULL THEN 1
                ELSE 0
            END) [Accepted]             
FROM (SELECT DISTINCT
       CustomerStatus.[Month],  
       accepted.CustomerID accepted,
       rejected.CustomerID rejected
FROM CustomerStatus
LEFT JOIN CustomerStatus accepted ON CustomerStatus.CustomerID = accepted.CustomerID AND accepted.Status = 'Accepted'
LEFT JOIN CustomerStatus rejected ON CustomerStatus.CustomerID = rejected.CustomerID AND rejected.Status = 'Rejected') CustomerStatus
GROUP BY [Month]

If you find my answer useful, I would appreciate if you mark as accepted and vote up.

user_192235860
Related