Home Forums SQL Server 2012 SQL 2012 - General Count(*) output blank but required Zero RE: Count(*) output blank but required Zero

  • February 28, 2017 at 2:39 am

    #1930636

    Sangeeth878787 - Tuesday, February 28, 2017 2:26 AM

    Hi Martin,

    I am glad for message, but when there is no data associated, the count will not return null, so isnull function won't work here. From the script you provided here, it will show count or sum of amount for 1970-01-01 to 1970-01-05 and 1970-01-06 but not for 1970-01-06. Please correct me If i am wrong.

    Thank you

    Martin has used ISNULL, as a SUM of NULLs or SUM of NO ROWS = NULL. You are using a COUNT, which means that COUNTof NULLs or COUNT of NO ROWS = 0.

    Notice that both myself and Martin have moved the order of your tables in your FROM clause, your Fact is now not the base table. Have you used the SQL I provided above, I am confident that it will work. Please don't amend it like above (using the fact table in the WHERE clause), run it exactly as it is, only replacing the underlined part of cast(Count(i.somefield AS varchar(100))) with a valid column name in your fact table.

    If it doesn't work, paste back what you ran again.and provide SAMPLE data. Please also remember to put SQL in code IFCode brackets, it is much easier to read 🙂
    Thanks.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk