4

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

  • what are you actually trying to do? essentially i'm asking why you dont put the where clause where it normally goes? – swasheck Dec 28 '16 at 17:10
10

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 ;
| improve this answer | |
0
  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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.