Count based on condition in SQL Server

Question

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:

UseeID    TotalcaseID    TotalRecordsWithSystem
1         7              2

Answer 1

Use SUM/CASE...

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

Answer 2

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