Rewriting the query

I have a table with the following sample data. The table actually contains more than 10 million rows.

I have to get a count of all the ids that only have type su1. If the id has su1 but also another type then it should not be counted. This is the query I came up with.

Select Count(*) From (
Select id
From table t
Where exists (select null from table t1 where t.id = t1.id and t1.type = 'su1')
Group by id
Having Count(*) = 1) a

tableid is the primary key. Id has a non-clustered index on it. Are there any other ways of writing this query?

I'm not entirely sure why you have Having Count(*) = 1 as it doesn't seem to be reflected in the requirements.

But this query is much better written as follows

SELECT COUNT(*)
FROM (
    SELECT id
    FROM [table] t
    GROUP BY id
    HAVING COUNT(CASE WHEN t1.type <> 'su1' THEN 1 END) = 0
) t;

And for that, you would need the following index

[table] (id) INCLUDE (type)