want to display zero if sum or count are having no value in column

Question

Here is my query to run find the grand totals.

I am just confusing here. Please let me know.

SELECT 
    CASE 
       WHEN SUBSTRING(FL.TRAN,1,2)='02' THEN 'TOTAL APPLES:'
       WHEN SUBSTRING(FL.TRAN,1,2)='01' THEN 'TOTAL BANANAS:'
       WHEN SUBSTRING(FL.TRAN,1,2)='40' THEN 'TOTAL ORANGES:'
       WHEN SUBSTRING(FL.TRAN,1,2)='31' THEN 'TOTAL GRAPES:'
    END TYPE,
    COUNT(FR.AMT) COUNT,
    ISNULL(SUM(FR.AMT),0) AMOUNT
FROM
    MYDB.FIN_FRUITLIST201706 AS FL 
JOIN 
    MYDB.FIN_FRUITRATE201706 AS FR ON FL.TSTAMP_TRANS = FR.TSTAMP_TRANS 
                                   AND FL.UNIQUENESS_KEY = FR.UNIQUENESS_KEY
WHERE 
    FL.TSTAMP_TRANS LIKE'201706%'
    AND SUBSTRING(FL.TRAN, 1, 2) IN ('02', '01', '40', '31')
GROUP BY
    CASE 
       WHEN SUBSTRING(FL.TRAN,1,2)='02' THEN 'TOTAL APPLES:'
       WHEN SUBSTRING(FL.TRAN,1,2)='01' THEN 'TOTAL BANANAS:'
       WHEN SUBSTRING(FL.TRAN,1,2)='40' THEN 'TOTAL ORANGES:'
       WHEN SUBSTRING(FL.TRAN,1,2)='31' THEN 'TOTAL GRAPES:'
    END

UNION ALL

SELECT 
    'GRAND TOTALS:' TYPE,
    COUNT(FR.AMT) COUNT,
    ISNULL(SUM(FR.AMT),0) AMOUNT
FROM
    MYDB.FIN_FRUITLIST201706 AS FL 
JOIN 
    MYDB.FIN_FRUITRATE201706 AS FR ON FL.TSTAMP_TRANS = FR.TSTAMP_TRANS 
                                   AND FL.UNIQUENESS_KEY = FR.UNIQUENESS_KEY
WHERE 
    FL.TSTAMP_TRANS LIKE'201706%'
    AND SUBSTRING(FL.TRAN, 1, 2) IN ('02', '01', '40', '31');

Here is a screenshot of the output:

If the totals are not having any value just want display as zero here.

Just want to put zero if no apples in table.

TOTAL APPLES: 0 0
TOTAL BANANAS: 3 6924
TOTAL ORANGES: 0 0
TOTAL GRAPES: 0 0

i am just confusing here. Please let me know.

i think that won't helpful . I just want to display zero '0' value if no value present for the totals. — Mdhar9e, Jul 24 '17 at 04:43
Are you looking for something like this?? https://stackoverflow.com/questions/2802713/changing-a-sum-returned-null-to-zero — Sowndarya, Jul 24 '17 at 04:53

score 0 · Answer 1 · answered Jul 24 '17 at 06:00

Please try this and check the result.

SELECT 
    CASE 
       WHEN SUBSTRING(FL.TRAN,1,2)='02' THEN 'TOTAL APPLES:'
       WHEN SUBSTRING(FL.TRAN,1,2)='01' THEN 'TOTAL BANANAS:'
       WHEN SUBSTRING(FL.TRAN,1,2)='40' THEN 'TOTAL ORANGES:'
       WHEN SUBSTRING(FL.TRAN,1,2)='31' THEN 'TOTAL GRAPES:'
    END TYPE,
    COUNT(FR.AMT) COUNT,
    ISNULL(SUM(FR.AMT),0) AMOUNT
FROM
    MYDB.FIN_FRUITLIST201706 AS FL 
LEFT JOIN 
    MYDB.FIN_FRUITRATE201706 AS FR ON FL.TSTAMP_TRANS = FR.TSTAMP_TRANS 
                                   AND FL.UNIQUENESS_KEY = FR.UNIQUENESS_KEY
WHERE 
    FL.TSTAMP_TRANS LIKE'201706%'
    AND SUBSTRING(FL.TRAN, 1, 2) IN ('02', '01', '40', '31')
GROUP BY
    CASE 
       WHEN SUBSTRING(FL.TRAN,1,2)='02' THEN 'TOTAL APPLES:'
       WHEN SUBSTRING(FL.TRAN,1,2)='01' THEN 'TOTAL BANANAS:'
       WHEN SUBSTRING(FL.TRAN,1,2)='40' THEN 'TOTAL ORANGES:'
       WHEN SUBSTRING(FL.TRAN,1,2)='31' THEN 'TOTAL GRAPES:'
    END

UNION ALL

SELECT 
    'GRAND TOTALS:' TYPE,
    COUNT(FR.AMT) COUNT,
    ISNULL(SUM(FR.AMT),0) AMOUNT
FROM
    MYDB.FIN_FRUITLIST201706 AS FL 
JOIN 
    MYDB.FIN_FRUITRATE201706 AS FR ON FL.TSTAMP_TRANS = FR.TSTAMP_TRANS 
                                   AND FL.UNIQUENESS_KEY = FR.UNIQUENESS_KEY
WHERE 
    FL.TSTAMP_TRANS LIKE'201706%'
    AND SUBSTRING(FL.TRAN, 1, 2) IN ('02', '01', '40', '31');

want to display zero if sum or count are having no value in column

1 Answers1