Count with where clause

Question

Is there a way to have WHERE clause in COUNT ? Or how to count with some clause ?

select 
  a
  b,
  count(c where t1.u='UAE') as c1 
from t1 

I am using MS SQL Server 2014

Answer 1

No, the syntax you have is not valid, it can be corrected by the use of a CASE expression.
(and I guess you have a GROUP BY a, b as you'd get an error otherwise).

select 
  a
  b,
  count(case when t1.u = 'UAE' then c else null end) as c1 
from t1 
group by a, b ;

Note that the ELSE NULL is redundant and can be removed as that is the default ELSE behaviour anyway:

select 
  a
  b,
  count(case when t1.u = 'UAE' then c end) as c1 
from t1 
group by a, b ;

---

There is a (SQL Standard) FILTER syntax that is close to your attempt but SQL Server has not yet implemented it:

select 
  a
  b,
  count(c) filter (where t1.u = 'UAE') as c1 
from t1 
group by a, b ;

Answer 2

  SELECT 
  DISTINCT
    a.a
    ,a.b
    ,b.c1
FROM
    t1 a
INNER JOIN (
            SELECT
                a
                ,b
                ,COUNT(u) AS c1
            FROM
                t1
            WHERE
                u = 'UAE'
            GROUP BY
                a
                ,b
            ) b
ON  a.a = b.a AND
    a.b = b.b

Just another way of doing it.