CODEWITHSUNDEEP
sqlsql-servert-sqlcountconditional-statements
Jin Yong
Jin Yong

Reputation: 43788

Count based on condition in SQL Server

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

Upvotes: 76

Views: 124000

Answers (4)

Alex
Alex

Reputation: 5656

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

Upvotes: 9

kage
kage

Reputation: 91

select
userid,
count('x') as TotalCaseID,
count(case when name = 'system' then 'x' else null end) as TotalRecordsWithSystem
from CustomerTable
group by userid

Upvotes: 9

Matt
Matt

Reputation: 171

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

Upvotes: 17

gbn
gbn

Reputation: 432667

Use SUM/CASE...

SELECT
    COUNT(*),  --total
    SUM(CASE WHEN name = 'system' THEN 1 ELSE 0 END) --conditional
FROM
    myTable

Upvotes: 159

Related Questions