How to display sum as 'o' if there are null amounts

Question

0.00/5 (No votes)

See more:

I am using the below query to display the amount as '0'

SQL

SELECT isnull(SUM(amount),0) as amount from getbillsfortaxinvoice WHERE code IN ('TT') and fno = '02112017416202742' and bill_desc not like '%GST%' GROUP BY folio_Id,room_no,guest_name

i have replaced the NULL values with '0' as using isnull. But the result is empty and not '0' after the sum

What I have tried:

can someone help me with this

i have also tried with

COALESCE() FUNCTION

but the amount is resulting as empty.

Posted 4-Feb-18 16:57pm

Member 13142345

Updated 4-Feb-18 21:51pm

PIEBALDconsult

v3

Add a Solution

Comments

PIEBALDconsult 4-Feb-18 23:54pm

Are there no records returned?

Member 13142345 5-Feb-18 0:29am

No records are returned. if there is an amount it is showing the amount but if there is no amount it is showing as empty

itsmypassion 5-Feb-18 1:27am

With GROUP BY you can not get NULL or 0 if no records found.Try to omit Group BY

Member 13142345 5-Feb-18 2:19am

when i omit the group by then the amount is showing as '0'. But the result is different from expected result as i have not written the group by

Laiju k 5-Feb-18 1:43am

can you give rows and columns values as it is shown.

Laiju k 5-Feb-18 1:53am

if no records then pass '0' value to amount in code behind.

2 solutions

Solution 1

MSDN wrote:
SUM can be used with numeric columns only. Null values are ignored.

So, replace:

SQL

isnull(SUM(amount),0)

with:

SUM(ISNULL(amount,1))

Note: A sum of zero's will get you... zero!

For further details, please see:
SUM (Transact-SQL) | Microsoft Docs[^]
ISNULL (Transact-SQL) | Microsoft Docs[^]

Posted 4-Feb-18 19:45pm

Maciej Los

Updated 4-Feb-18 19:47pm

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

itsmypassion · Accepted Answer · 2018-02-04T21:51:00

Solution 2

Try it

SQL

SELECT ISNULL(SUM(AMOUNT),0) FROM(SELECT isnull(SUM(amount),0) as amount from getbillsfortaxinvoice WHERE code IN ('TT') and fno = '02112017416202742' and bill_desc not like '%GST%' GROUP BY folio_Id,room_no,guest_name) as X

Posted 4-Feb-18 21:51pm

itsmypassion

Comments

Member 13142345 6-Feb-18 4:34am

It solves the problem and the result is '0' if there is an empty value.
But the field name is not displaying as amount. it is showing as (No Column Name).

so i modified it as
SELECT ISNULL(SUM(AMOUNT),0) as amount FROM(SELECT isnull(SUM(amount),0) as amount from getbillsfortaxinvoice WHERE code IN ('TT') and fno = '02112017416202742' and bill_desc not like '%GST%' GROUP BY folio_Id,room_no,guest_name) as X

Thank You for the query