Don't Count Null

• demitri_baran

  SSC-Addicted

  Points: 423

  More actions

  October 28, 2009 at 9:44 am

  #139709

  My measure group contains only a single measure that applies a distinct count aggregation, that also counts null as 1. Is there a way to not count the null records.

  eg. Country Desc

  USA

  England

  null

  The distinct count measure group result is 3

  The desired result is 2

• Lewis Dowson

  SSC Eights!

  Points: 988

  More actions

  October 28, 2009 at 9:55 am

  #1071865

  Hi,

  You could just add a WHERE condition to say:

  WHERE country != 'null'

  I've noticed that you are storing the null value as text, i.e. 'null'.

  If you stored the null as an actual NULL data entry then you wouldn't have this problem.

  I would suggest that you update all your 'null' text values to a NULL data entry. This would save on space and remove your problem.

  UPDATE CountryTable

  SET CountryColumn = NULL

  WHERE CountryColumn = 'null'

  Below is an example of what I mean between 'null' and NULL.

  There are 4 values inserted, one of each type of null value. You'll notice that with my suggestion above you would get the correct value.

  create table #t (t varchar(10))

  insert into #t

  select 'usa'

  insert into #t

  select 'uk'

  insert into #t

  select 'null'

  insert into #t

  select NULL

  select COUNT(t)

  from #t

  drop table #t

  Hope that helps,

  Lewis

• Ells

  SSCrazy Eights

  Points: 9901

  More actions

  November 3, 2009 at 6:12 am

  #1074229

  or on the fact table add a column of value 1. This means that you can always retrieve the number of records. Very usefull thing to do generally.

  Mark.

  

• aedna-843346

  SSC Enthusiast

  Points: 177

  More actions

  November 29, 2011 at 12:17 pm

  #1414285

  I had the same problem.

  I solved it by creating a new view from exisitng fact table with a WHERE condition ISNULL(Country_Code,0) <> 0 and then creating a Distinct Count of Country_Code out of this view