How do I count distinct to exclude a value?

Question

Below is the different scales in a POS system. I am trying to count the number of distinct scales that are not 'MANUAL WT'.

This is what I have, but it is returning 2 and not 6. count (distinct (case when d.SCALE_IN_ID != 'MANUAL WT' then 1 else 0 end)) as Num_Scale

Tag your question with the database you are using. – Gordon Linoff Mar 11 '20 at 18:15 — Gordon Linoff, Mar 11 '20 at 18:15

score 3 · Answer 1 · answered Mar 11 '20 at 18:13

Consider:

select count(distinct case when scale_in_id <> 'MANUAL WT' then scale_in_id end) cnt
from mytable

The problem with your original query is that the case expression turns values to either 0 and 1, and then the aggregate function computes how many distinct values are returned: since values are all 0s or 1s, there are only two distinct values (or one in edge cases): hence the result that you are getting.

score 1 · Answer 2 · answered Mar 11 '20 at 18:16

1

A simple WHERE clause will do:

select count(distinct scale_in_id) Num_Scale
from tablename
where scale_in_id <> 'MANUAL WT'

answered Mar 11 '20 at 18:16

forpas

160,666
10
38
76

score 0 · Answer 3 · edited May 25 '23 at 06:08

0

The value that you want to exclude may be replaced by NULL. Count excludes NULL:

select count(distinct case when scale_in_id <> 'MANUAL WT' then scale_in_id else NULL end) cnt
from mytable

edited May 25 '23 at 06:08

Adriaan

17,741
7
42
75

answered May 24 '23 at 23:03

Gaguimo

1

How do I count distinct to exclude a value?

3 Answers3