Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Coalesce returning 1 value only Expand / Collapse
Author
Message
Posted Wednesday, September 16, 2009 12:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:08 AM
Points: 6, Visits: 50
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.
Post #789202
Posted Wednesday, September 16, 2009 12:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 2:01 PM
Points: 1,207, Visits: 1,277
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.
Post #789204
Posted Wednesday, September 16, 2009 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:08 AM
Points: 6, Visits: 50
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

Post #789213
Posted Wednesday, September 16, 2009 12:42 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 2:01 PM
Points: 1,207, Visits: 1,277
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.
Post #789216
Posted Wednesday, September 16, 2009 12:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:56 PM
Points: 20,806, Visits: 32,739
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 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)
Post #789222
Posted Wednesday, September 16, 2009 12:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 2:01 PM
Points: 1,207, Visits: 1,277
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

Post #789223
Posted Wednesday, September 16, 2009 1:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:08 AM
Points: 6, Visits: 50
Thanks again, Matt.
That worked perfectly.

Regards,
Afkas
Post #789254
Posted Wednesday, September 16, 2009 7:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #789371
Posted Thursday, September 17, 2009 3:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:44 AM
Points: 1,260, Visits: 3,424
Yea nice trick Jeff!


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #789481
Posted Thursday, September 17, 2009 4:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555
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



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #789515
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse