How to return 'blank' using Sum function in Sql, if Sum is resulting 0 in SQL

Question

I am using Sql Server 2008. I am adding some column value using Sum function. Like the code below:

 SELECT 'RCOAuthorizer LMS',
'' AS Consumer_Loan,
'' AS Auto_Loan,
'' AS Credit_Card,
SUM(CASE
       WHEN sq2.loan_type = 'Loan Amendment' THEN sq2.user_count
       ELSE ''
   END ) AS Loan_Amendment,
SUM(CASE
       WHEN sq2.loan_type = 'Pre-Payment' THEN sq2.user_count
       ELSE ''
   END ) AS Pre_Payment,
SUM(CASE
       WHEN sq2.loan_type = 'Corporate Credit card' THEN sq2.user_count
       ELSE ''
   END ) AS Corporate_Credit_card,
'' AS Auto_Payment_Release,
'' AS Car_Mulkiya
 FROM
( SELECT 'RCOAuthorizer' AS ws_name,
       'Loan Amendment' AS loan_type,
       COUNT (DISTINCT a.bpm_referenceno) AS user_count,
             a.user_id AS user_id
FROM BM_LMS_DecisionHistoryGrid a
INNER JOIN
 ( SELECT m.bpm_referenceno
  FROM BM_LMS_EXTTABLE m
  WHERE m.request_type = 'Loan Amendment' ) sq1 ON a.bpm_referenceno = sq1.bpm_referenceno
WHERE workstep_name = 'RCOAuthorizer'
GROUP BY a.user_id
UNION SELECT 'RCOAuthorizer',
            'Pre-Payment',
            COUNT (DISTINCT a.bpm_referenceno), a.user_id
FROM BM_LMS_DecisionHistoryGrid a
INNER JOIN
 ( SELECT m.bpm_referenceno
  FROM BM_LMS_EXTTABLE m
  WHERE m.request_type = 'Pre-Payment' ) sq1 ON a.bpm_referenceno = sq1.bpm_referenceno
WHERE workstep_name = 'RCOAuthorizer'
GROUP BY a.user_id
UNION SELECT 'RCOAuthorizer',
            'Corporate Credit card',
            COUNT (DISTINCT a.bpm_referenceno), a.user_id
FROM BM_LMS_DecisionHistoryGrid a
INNER JOIN
 ( SELECT m.bpm_referenceno
  FROM BM_LMS_EXTTABLE m
  WHERE m.request_type = 'Corporate Credit card' ) sq1 ON a.bpm_referenceno = sq1.bpm_referenceno
WHERE workstep_name = 'RCOAuthorizer'
GROUP BY a.user_id ) sq2
GROUP BY sq2.ws_name

The above query will return Sum of all the numbers available in 'a' column. But in case, there is no record, then it will return '0' as result.

I require that if there is no record, it must show blank instead of showing '0'. How to handle the same.

By "there is no record" do you actually mean there is a record where column a is null? If there is no record, there would be no sum too.... — George Menoutis
– George Menoutis, Commented May 31, 2018 at 8:59
No record means 0. In the above code, it will never return Null as I am already handling it by using isnull() function. — Md Kamran Azam
– Md Kamran Azam, Commented May 31, 2018 at 9:03

EzLo · Accepted Answer · 2018-05-31 09:38:15Z

1

To start, you don't need an ISNULL with a back value of 0 (the neutral for adding) inside a SUM aggregate, as the SUM already ignores NULL values. So SUM(ISNULL(Column, 0)) is equal to SUM(Column) (but different from ISNULL(SUM(Column), 0)!).

Seems that you want a VARCHAR result instead of a numeric one. You can solve this with a CASE.

Select 
    CASE WHEN Sum(a) = 0 THEN '' ELSE CONVERT(VARCHAR(100), Sum(a)) END
from 
    table;

If you don't want to repeat the SUM expression:

;WITH SumResult AS
(
    Select CONVERT(VARCHAR(100), Sum(a)) AS SumTotal 
    from table
)
SELECT
    CASE WHEN R.SumTotal = '0' THEN '' ELSE R.SumTotal END
FROM
    SumResult AS R

Keep in mind that in these both cases, if there is no record to calculate the sum from, the result will be NULL.

EDIT: There is no point in adding '' inside your SUM, as it's converted to 0 to be able to sum. The solution is still the same as I posted before.

Change

SUM(CASE
       WHEN sq2.loan_type = 'Pre-Payment' THEN sq2.user_count
       ELSE ''
   END ) AS Pre_Payment,

for

CASE 
    WHEN SUM(CASE WHEN sq2.loan_type = 'Pre-Payment' THEN sq2.user_count END) = 0 THEN ''
    ELSE CONVERT(VARCHAR(100), SUM(CASE WHEN sq2.loan_type = 'Pre-Payment' THEN sq2.user_count)) END AS Pre_Payment,

edited May 31, 2018 at 9:38

answered May 31, 2018 at 9:04

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Md Kamran Azam Over a year ago

I have updated the code with actual code. In this, the prepayment column displays '0' as there is no value. I want to return blank. Please check.

EzLo Over a year ago

@MdKamranAzam edited, check now. The solution still applies.

Barbaros Özhan · Accepted Answer · 2018-05-31 09:02:59Z

1

Just try this ( use isnull again ):

Select isnull(Sum(isnull(a,0)),0) from table_;

I used table_ instead of table, because table is a reserved keyword.

SQL Fiddle Demo

answered May 31, 2018 at 9:02

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

Md Kamran Azam Over a year ago

I have updated the code with actual code. In this, the prepayment column displays '0' as there is no value. I want to return blank. Please check.

Barbaros Özhan Over a year ago

@MdKamranAzam your question text still says : But in case, there is no record, then it will return '0' as result.

Collectives™ on Stack Overflow

How to return 'blank' using Sum function in Sql, if Sum is resulting 0 in SQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related