SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Coalesce returning 1 value only


Coalesce returning 1 value only

Author
Message
apalmer
apalmer
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 88
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.
matt6288
matt6288
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 1374
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
apalmer
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 88
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
matt6288
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 1374
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
Lynn Pettis
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: General Forum Members
Points: 150758 Visits: 39285
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).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
matt6288
matt6288
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 1374
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
apalmer
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 88
Thanks again, Matt.
That worked perfectly.

Regards,
Afkas
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340495 Visits: 42644
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. :-P

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Dugi
Dugi
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7168 Visits: 3511
Yea nice trick Jeff!
;-)

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Mark Cowne
Mark Cowne
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10166 Visits: 26054
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. :-P


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




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search