Count based on condition in SQL Server

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Example:

How can I do a column count for records with name 'system', and total CaseID records in the table?

Customer table

UserID     CaseID     Name
1          100        alan
1          101        alan
1          102        amy
1          103        system
1          104        ken
1          105        ken
1          106        system  
The result will display like below:
UserID    TotalCaseID    TotalRecordsWithSystem
1         7              2
                Oh the old good SUM CASE... Always so helpful, always so easy to forget about. Thank you!
– Tiago César Oliveira
                Nov 16, 2016 at 10:19
                @SebastianJ. probably because you used COUNT() on the CASE WHEN .. THEN .. ELSE .. END construction. If you use SUM() as shown it will work just fine.
– deroby
                Jan 4, 2019 at 17:46
    userid,
    COUNT(*) as TotalcaseID, --total 
    SUM(CASE WHEN name = 'system' THEN 1 ELSE 0 END) as TotalRecordsWithSystem  
    myTable 
group by userid
userid,
count('x') as TotalCaseID,
count(case when name = 'system' then 'x' else null end) as TotalRecordsWithSystem
from CustomerTable
group by userid
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.