Count based on condition in SQL Server
SqlSql ServerTsqlSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
Use SUM/CASE...
SELECT
COUNT(*), --total
SUM(CASE WHEN name = 'system' THEN 1 ELSE 0 END) --conditional
FROM
myTable
Solution 2 - Sql
I think he wanted user id in the results
SELECT
userid,
COUNT(*) as TotalcaseID, --total
SUM(CASE WHEN name = 'system' THEN 1 ELSE 0 END) as TotalRecordsWithSystem
FROM
myTable
group by userid
Solution 3 - Sql
select
userid,
count('x') as TotalCaseID,
count(case when name = 'system' then 'x' else null end) as TotalRecordsWithSystem
from CustomerTable
group by userid
Solution 4 - Sql
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