If null return 0 in sql server

I want it to return the sum for bob and john AND return 0 for jane. You can feed it a list, and it'll return the first non-null value in the list. You would use this in your query as follows:. If you only select from the Transactions table, there is no way to get information about things not in the table. However, by using a left join from the Tenants table, you should get a row for every existing tenant.

Below is a full walkthrough of the problem. The function isnull has also been included to ensure that a balance of zero rather than null is returned for Tenants with no transactions. I only added this because if you're intention is to take into account for one of the parts being null you'll need to do the ISNULL separately.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How can I get this query to return 0 instead of null? Ask Question. Asked 12 years, 8 months ago.

Active 12 years, 8 months ago. Viewed 13k times. Improve this question. Malfist Malfist Rewrote the query in my answer, I misunderstood exactly what you were asking for.

Add a comment. Active Oldest Votes. How can I replace it with 0? When you want to replace a possibly null column with something else, use IsNull. Data type determination of the resulting expression is different. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.

Although, where summing when condition then 1 , you could just as easily change sum to count - eg:. When you say the first three columns, do you mean your SUM columns?

Add an else to your case statements so that they default to zero if the test condition is not found. Adding anything to Null will result in Null, and that is the issue with this query. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 8 years, 7 months ago. Active 16 days ago. Viewed k times. Select c. Improve this question. Bhaskar Mishra Bhaskar Mishra 2, 6 6 gold badges 21 21 silver badges 34 34 bronze badges. Thank you Jonathan! This worked! This will return a 0, instead of a null, if the inner select does not return any records.

Keep in mind that this is not a good query to be running as a part of a production code set. You should change this out to be a parameterized query like the following:. Then you bind the value to the parameter. The benefit here is that you only cache one plan in the plan cache for this.

You can turn on forced parameterization in SQL , but it is best to write your code to use parameters instead. If you already realized this then please disregard this post, I just wanted to cover this in case you don't realize this. Thank you Jonathan, I chose a simplified example to help me formulate my question in a concise way. My production code query is all parameterized and is much more complex than the query above.

You helped me out big time. Big booooyahhh to you. The content you requested has been removed. Ask a question. Quick access.