Coalesce returning 1 value only

  • I apologise in advance if this question was asked and answered before.

    I have a query similar to the one shown below:

    select A.grading, coalesce(count(B.IDno), count(C.IDno)) as myCount

    From A

    left join B on A.fkIDno = B.IDno and A.grading = 1

    left join C on A.fkIDno = C.IDno and A.grading = 2

    Where A.grading > 3

    However, when I run the query, I get the correct count result for table B and 0 for table C. And if I change where table B and C are in the query (as shown below)

    select A.grading, coalesce(count(C.IDno), count(B.IDno)) as myCount

    From A

    left join C on A.fkIDno = C.IDno and A.grading = 2

    left join B on A.fkIDno = B.IDno and A.grading = 1

    Where A.grading > 3

    I get correct count for table C and 0 for table B.

    Any assistance on this will be greatly appritiated.

    Many thanks in advance.

    Afkas.

  • From BOL: COALESCE Returns the first nonnull expression among its arguments.

    So in your case the first argument will always be nonnull so it will be the one returned.

  • Thanks, Matt.

    I know for each row, it will return only the non-Nulls.

    But my problem is that for all other rows which are non-Nulls, the query produces 0 as the count.

    E.g.

    Row 1 - 5 (Count from table B)

    Row 2 - 0 (Count from table C)

    Row 3 - 2 (Count from table B)

    Row 4 - 6 (Count from table B)

    Row 2 should have returned a value other than 0

    Regards,

    Afkas

  • The problem is that a count of 0 is still nonnull so that is what coalesce returns. You could probably do something with a case statement that would correct this.

  • Please take the time to read and follow the instructions in the first article I have referenced below in my signature block regarding asking for assistance. Without being there to see your tables, data, and knowing what is actually expected from your query, there really isn't much we can do to help. We need the DDL for your tables, some sample data that is representative of your actual data and the problem, expected results based on the sample data, and where appropriate the code you have written so far (and that I believe you have already provided).

  • Lynn's right having the table structures and sample data does make helping out a lot easier. But I think I understand the problem at least well enough to throw out a sample query.

    Would this get the results you are looking for?

    select A.grading, CASE WHEN count(B.IDno) = 0 THEN count(C.IDno) ELSE COUNT(B.IDno) end as myCountFrom A

    left join B on A.fkIDno = B.IDno and A.grading = 1

    left join C on A.fkIDno = C.IDno and A.grading = 2

    Where A.grading > 3

  • Thanks again, Matt.

    That worked perfectly.

    Regards,

    Afkas

  • Matt is certainly on the right track... but you don't need a CASE statement...

    select A.grading, ISNULL(NULLIF(count(B.IDno),0),COUNT(B.IDno)) as myCount

    From A

    left join B on A.fkIDno = B.IDno and A.grading = 1

    left join C on A.fkIDno = C.IDno and A.grading = 2

    Where A.grading > 3

    As a sidebar, I used ISNULL instead of COALESCE because ISNULL is a bit faster across a million rows. If you believe in the MYTH of code portability, then you should change it back to COALESCE. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yea nice trick Jeff!

    😉

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Jeff Moden (9/16/2009)


    Matt is certainly on the right track... but you don't need a CASE statement...

    select A.grading, ISNULL(NULLIF(count(B.IDno),0),COUNT(B.IDno)) as myCount

    From A

    left join B on A.fkIDno = B.IDno and A.grading = 1

    left join C on A.fkIDno = C.IDno and A.grading = 2

    Where A.grading > 3

    As a sidebar, I used ISNULL instead of COALESCE because ISNULL is a bit faster across a million rows. If you believe in the MYTH of code portability, then you should change it back to COALESCE. 😛

    Just for fun...

    select A.grading, (1-SIGN(COUNT(B.IDno)))*COUNT(C.IDno) + COUNT(B.IDno) as myCount

    From A

    left join B on A.fkIDno = B.IDno and A.grading = 1

    left join C on A.fkIDno = C.IDno and A.grading = 2

    Where A.grading > 3

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thanks everyone.

    Just out of curiosity, how come coalesce didnt work?

  • As Matt wrote in an earlier post, the COALESCE statement *is* working.

    SELECT COALESCE(0, 15) will return 0. SELECT COALESCE(NULL, 15) will return 15. To return the first non-null, non-zero value, use SELECT COALESCE(NULLIF(Value1, 0), Value2), as suggested earlier.

Viewing 12 posts - 1 through 11 (of 11 total)

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