Coalesce returning 1 value only

Question

Post reply

Coalesce returning 1 value only

apalmer

Old Hand

Points: 372
More actions
September 16, 2009 at 12:19 pm

#210828

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.

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

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

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.

apalmer Old Hand Points: 372 More actions · Answer 2

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 3

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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).

matt6288 SSCertifiable Points: 5058 More actions · Answer 5

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

apalmer Old Hand Points: 372 More actions · Answer 6

Thanks again, Matt.

That worked perfectly.

Regards,

Afkas

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

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)

Dugi SSCoach Points: 17998 More actions · Answer 8

Yea nice trick Jeff!

😉

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 9

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

apalmer Old Hand Points: 372 More actions · Answer 10

Thanks everyone.

Just out of curiosity, how come coalesce didnt work?

PhilPacha Hall of Fame Points: 3645 More actions · Answer 11

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.