This is a refinement of my previous problem. I have the following
table :

C0 | C1 | C2
----+----+----
A | 1 | X
----+----+----
A | 1 | X
----+----+----
A | 2 | X
----+----+----
A | 1 | Y
----+----+----
B | 1 | X
----+----+----
B | 1 | X

I want to write a request which counts the number of different (C0,
C1, C2) where C2 is X. Here, the result should be 3. What would be the
request ? A kind of :

select count(*) from (select distinct C0, C1 from T where C2 = 'X')

[quoted text, click to view]

You need to give your table expression a correlation name:

select count(*) from (select distinct C0, C1 from T where C2 = 'X') as t

You almost had it :-)

select count(*) from (select distinct C0, C1 from T where C2 = 'X') AS X

An alias is mandatory for a derived table even if it isn't referenced
anywhere.

--
David Portas
SQL Server MVP
--