Post reply

countdistinct of 2 null values returns 1

  • September 30, 2009 at 5:29 am

    #226687

    Hello,

    I have a problem which I'm failing to understand. In a matrix, I'm doing a countdistinct of two fields (i.e. countdistinct(Fields!x.Value & Fields!y.Value)). For one of my column groups this should definitely be returning 0, as both fields are null for this group. I proved this to myself by doing a count of each field individually, and these correctly returned 0. But together for some reason they return 1.

    I then tried replacing one of the fields with Nothing (which I understand to be the SSRS expression equivalent of null), and this also returns 1. I furthered this by doing a simple countdistinct(Nothing) which returns 0, and then tried countdistinct(Nothing & Nothing), which returns 1.

    I'm confused because I thought countdistinct was supposed to return a count of all non-null values, and these values are clearly null! If anyone could offer an explanation I'd be very grateful.

    Many thanks,

    Peter

  • September 30, 2009 at 8:25 am

    #1059980

    Investigating this a tad further, it seems that if you do a countdistinct on any two amalgamated fields, null or otherwise (using + or &), it gives a count that is 1 higher than the correct count. Is this a bug, maybe?

  • September 30, 2009 at 10:01 am

    #1060064

    According to SQL-92 - two NULLs cannot be deemed distinct from each other (which is why you would see a *single* NULL in the group value for an Aggregation query - all of the NULL rows get aggregated together because they aren't distinct from each other).

    By using Count (DISTINCT X), you're now counting "grouping levels" and NOT column values, which is why the NULL now gets counted.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 1, 2009 at 1:59 am

    #1060315

    Ah right - I suppose null + null doesn't = null using ternary logic.

    Thanks for that!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply