Getting SELECT to return zero in both cases of NULL and EMPTY row in single SQL query

Question

The following query results value as single row either as value for ExtendedText or zero for matching LineNumber in the table data. I need to add one more condition in the query as - if the LineNumber doesn't exist return one row as zero as same in the case of null. The query should check both for NULL and empty to return zero

SELECT  ISNULL(Sum(convert(dec,DeliveryPaymentExtras.ExtendedText)),0) As ExtendedText
FROM    DeliveryPaymentExtras
WHERE   (LineNumber =21) group by LineNumber

@ypercube Yep .. that worked for me:-) Please edit your comment as answer — Murali Uppangala, Nov 06 '13 at 06:32

ypercubeᵀᴹ · Accepted Answer · 2013-11-06T06:41:52.320

If you want the result for only one LineNumber value, as the code suggests, it's trivial. Just remove the GROUP BY LineNumber. An aggregation without group by means that the result will be exactly one row, no matter if we have 0 or a million rows, before the aggregation:

SELECT  ISNULL(Sum(convert(dec,DeliveryPaymentExtras.ExtendedText)),0) AS ExtendedText
FROM    DeliveryPaymentExtras
WHERE   (LineNumber =21) ;

If you want results for multiple values (for example if you had: WHERE LineNumber IN (21, 34, 55) GROUP BY LineNumber), then it's not straightforward. One way is:

SELECT  v.LineNumber,
        ISNULL(Sum(convert(dec,d.DeliveryPaymentExtras.ExtendedText)),0) 
            AS ExtendedText
FROM    ( VALUES (21),(34),(55)
        ) AS v (LineNumber)
    LEFT JOIN DeliveryPaymentExtras AS d
        ON d.LineNumber = v.LineNumber
GROUP BY v.LineNumber ;

+1 had not noticed your second answer was similar to the answer i posted (and deleted) — t-clausen.dk, Nov 06 '13 at 09:36

score 2 · Answer 2 · answered Nov 06 '13 at 06:33

Select your value into a variable, and select that variable.

Your issue is that NULL number of rows will not trigger your ISNULL which evaluates the SUM and not the number of rows in the query. It's a different mechanic.

So you'll need to select your ISNULL sum into a variable, and then select from that in your query:

Something like this:

DECLARE @Result DECIMAL(18,4)
SET @Result = 
(
SELECT  Sum(convert(dec,DeliveryPaymentExtras.ExtendedText)) As ExtendedText
FROM    DeliveryPaymentExtras
WHERE   (LineNumber =21) group by LineNumber
(


SELECT ISNULL(@Result, 0)

Getting SELECT to return zero in both cases of NULL and EMPTY row in single SQL query

2 Answers2