Count based on condition in SQL Server

user_52745 0
Viewed 98420

Does anyone know how can I do a count in SQL Server based on condition.

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
tsql sql-server sql
4 Answers

If you're on SQL Server 2012+, then you can use SUM/IIF

SELECT
    COUNT(*) AS Total,
    SUM(IIF(Name = 'system', 1, 0)) AS SystemTotal
FROM
    CustomerTable
user_3135280
Related