CODEWITHSUNDEEP
sqlsql-server-2008t-sqlis-emptyselect-query
Murali Uppangala
Murali Uppangala

Reputation: 904

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

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

Upvotes: 0

Views: 1369

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

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 ;

Upvotes: 3

Allan S. Hansen
Allan S. Hansen

Reputation: 4091

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)

Upvotes: 2

Related Questions