Results 1 to 3 of 3
  1. #1
    Join Date
    May 2007
    Posts
    4

    Unanswered: Distinct Count With Null Values (grand Total)

    Hello,

    I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
    Measures:
    Measure group Professors:
    Amount of projects (COUNT proj_id)
    Amount of publications (COUNT pub_id)
    Amount of e_books (COUNT book_id)
    --------------
    Measure group Projects:
    Distinct amount of projects (DISTINCT COUNT proj_id)
    --------------
    Measure group Publications:
    Distinct amount of publications (DISTINCT COUNT pub_id)
    --------------
    Measure group E_books:
    Distinct amount of e_books (DISTINCT COUNT book_id)
    Calculated measures:
    Amnt_Projects
    iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
    Amnt_Publications
    (similar to the above one)
    Amnt_E_books
    (similar to the above one)
    ---------------------------
    Dimensions:
    dimPROFESSORS
    - prof_id
    -surname
    -name
    -gender
    dimPROJECTS
    - proj_id
    -type name
    -name
    dimPUBLICATIONS
    - pub_id
    -type name
    -name
    dimE_BOOKS
    - book_id
    -name
    Date_Projects
    -date_id
    -years
    Date_Publications
    -date_id
    -years
    Date_E_books
    -date_id
    -years


    For example, when I browse the cube:
    prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
    1032------------------- 30 --------------------------1----------------1
    1070------------------- 90 --------------------------2----------------2
    1111------------------- 0 ---------------------------1----------------0
    1137------------------- 0 ---------------------------1----------------0
    1234-------------------1404--------------------------9----------------9
    1721------------------- 504--------------------------7----------------7
    2661------------------- 85 --------------------------5----------------5
    ...-------------------- ...---------------------------...----------------...
    6999------------------- 20---------------------------1-----------------1
    9956------------------- 50---------------------------5-----------------5
    Unknown----------------(empty)---------------------(empty)-----------0
    Grand Total------------ 2421------------------------11-----------------11


    Grand Total 11 is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be 59 if for all professors). How could I get the right value to be shown in Grand Total?

  2. #2
    Join Date
    May 2007
    Posts
    4
    Any suggestions?

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you want to include nulls in a count in t-sql, you can do something like this:

    select count(distinct coalesce(mycolumn, 'THIS COLUMN IS NULL')) from mytable

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •