Count column with specific condition

Question

How can i count columns that hold particular values - but have it as a grand-total.

table data:

 Code      No
  1         *
  2         -
  3         4
  4         

If for example i wanted to count how many rows had * and - and space

I could do

Case when No = '*'
Then COUNT(No)
when No = '-' then count(No)
when No = '' then count(No)
else 0 end as 'Count'

but this returns 4

http://sqlfiddle.com/#!9/f73409/4

I would want this to return 3

Any help would be appreciated

Answer 1

Standard SQL has a particular feature for that: the filter clause that follows aggregates.

Unfortunately, it is not very widely supported (mainly PostgreSQL).

There is an easy workaround using case however:

COUNT(CASE WHEN <condition> THEN 1 END)

This works because the implied else null clause of case and because count does not count null.

More about the filter clause and ways to mimic it: http://modern-sql.com/feature/filter

Answer 2

Use IN:

select Sum(Case when No IN ('*', '-', '') then 1 else 0 end) as Count
from Table1

See Fiddle.