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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
No, the syntax you have is not valid, it can be corrected by the use of a
(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 ;