Count based on condition in SQL Server

Use SUM/CASE…

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