Sum inner join if didn't exist = 0-CodePudding

I have a task to make sum of this and I want to get sum value = 0 if it didn't match with the join table.

SELECT 
    TCS.departement,
    TCS.category,
    FORMAT ( BE.DT , 'yyyy-MM' ) AS CURR_MONTH,
    ISNULL(SUM(CASE 
            WHEN BE.DFROM IN('SEWING','WEAVING','FORMING',
            'DYE','HSM','INSPECTION','WOVEN') 
            AND BE.DFROM = BE.DTO THEN BE.QTY
        END
        ),0) AS TOTAL_LOSS_PROSES,
    ISNULL(SUM(CASE
            WHEN  DFROM != 'SEWING' THEN QTY 
            WHEN  DFROM != 'WEAVING'THEN QTY 
            WHEN  DFROM != 'FORMING'THEN QTY 
            WHEN  DFROM != 'DYE'THEN QTY 
            WHEN  DFROM != 'HSM'THEN QTY 
            WHEN  DFROM != 'INSPECTION'THEN QTY 
            WHEN  DFROM != 'WOVEN'THEN QTY 
            ELSE 0 
        END),0) AS TOTAL_LOSS_SEMUA
    FROM TBL_CATEGORY TCS
    INNER JOIN  
    B_ENTRYLOSS BE ON  TCS.category = BE.LOSSCAT
    WHERE 
    FORMAT ( BE.DT , 'yyyy-MM') BETWEEN '2022-04' AND '2022-06'
    GROUP BY TCS.departement, TCS.category, 
    FORMAT ( BE.DT , 'yyyy-MM') 
    order by category ASC, CURR_MONTH DESC;

my result :

    |departement|   category   |CURR_MONTH|TOTAL_LOSS_PROSES|TOTAL_LOSS_SEMUA|
    |:----------|:-------------|:---------|:----------------|:---------------|
    |WEAVING    |    Anyaman   |  2022-04 |      88.80      |   181.69       |
    |WEAVING    |    Anyaman   |  2022-05 |      86.64      |   126.02       |
    |WEAVING    |    Anyaman   |  2022-06 |      80.80      |   117.55       |
    |DYE        |    Belang    |  2022-04 |      0.00       |   0.67         |
    |DYE        |    Belang    |  2022-05 |      0.00       |   0.67         |
    |WEAVING    |Benang Keluar |  2022-05 |      24.11      |   24.11        |
    |WEAVING    |Benang Keluar |  2022-06 |      7.65       |   7.65         |

expected result:

|departement|   category   |CURR_MONTH|TOTAL_LOSS_PROSES|TOTAL_LOSS_SEMUA|
|:----------|:-------------|:---------|:----------------|:---------------|
|WEAVING    |    Anyaman   |  2022-04 |      88.80      |   181.69       |
|WEAVING    |    Anyaman   |  2022-05 |      86.64      |   126.02       |
|WEAVING    |    Anyaman   |  2022-06 |      80.80      |   117.55       |
|DYE        |    Belang    |  2022-04 |      0.00       |   0.67         |
|DYE        |    Belang    |  2022-05 |      0.00       |   0.67         |
|DYE        |    Belang    |  2022-06 |      0.00       |   0.00         |
|WEAVING    |Benang Keluar |  2022-04 |      0.00       |   0.00         |
|WEAVING    |Benang Keluar |  2022-05 |      24.11      |   24.11        |
|WEAVING    |Benang Keluar |  2022-06 |      7.65       |   7.65         |

Please help me with this code, should I use coalesce to change isnull?

why i didn't use a single expression to both sum, because the result i get with the query form single expression didn't match

SUM(CASE WHEN  DFROM NOT IN( 'SEWING','WEAVING','FORMING','DYE','HSM','INSPECTION','WOVEN') 
 THEN QTY 
 ELSE 0

result :

        |departement|   category   |CURR_MONTH|TOTAL_LOSS_PROSES|TOTAL_LOSS_SEMUA|
        |:----------|:-------------|:---------|:----------------|:---------------|
        |WEAVING    |    Anyaman   |  2022-04 |      88.80      |   27.20       
        |WEAVING    |    Anyaman   |  2022-05 |      86.64      |   22.87       
        |WEAVING    |    Anyaman   |  2022-06 |      80.80      |   13.70       
        |DYE        |    Belang    |  2022-04 |      0.00       |   0.67         
        |DYE        |    Belang    |  2022-05 |      0.00       |   0.67         
        |WEAVING    |Benang Keluar |  2022-05 |      24.11      |   0.00 
        |WEAVING    |Benang Keluar |  2022-06 |      7.65       |   0.00

its different from the first query when i put whole list of case branches, i still didn't know the reason why got different results

CodePudding user response：

If there is no data for the month "2000-06" in the inner joined table then there will be no row produced by the group by clause, and hence it won't matter if you use ISNULL or COALESCE (because there simply is no row for either function to work with).

So you must "force" every wanted row to exist, then "outer join" your data . Below you can see that I have formed 3 month values in a "derived table" that is used with a cross join which forces every "month" to be present in the result. Then these "months" are then used as part of the join condition to your loss source data. Now even if there is no matching source data you will still get the month displayed.

SELECT
      TCS.departement
    , TCS.category
    , m.mnth AS CURR_MONTH
    , COALESCE(SUM(CASE 
                WHEN BE.DFROM IN ('SEWING', 'WEAVING', 'FORMING', 'DYE', 'HSM', 'INSPECTION', 'WOVEN') AND BE.DFROM = BE.DTO
                    THEN BE.QTY
                END), 0) AS TOTAL_LOSS_PROSES
    , COALESCE(SUM(CASE 
                WHEN BE.DFROM IN ('SEWING', 'WEAVING', 'FORMING', 'DYE', 'HSM', 'INSPECTION', 'WOVEN')
                    THEN 0
                ELSE BE.QTY
                END), 0) AS TOTAL_LOSS_SEMUA
FROM TBL_CATEGORY TCS
CROSS JOIN (
    SELECT '2022-04' AS mnth        
    UNION ALL 
    SELECT '2022-05'
    UNION ALL
    SELECT '2022-06'
    ) AS m
LEFT OUTER JOIN B_ENTRYLOSS BE ON TCS.category = BE.LOSSCAT
                              AND FORMAT(BE.DT, 'yyyy-MM') = m.mnth
GROUP BY TCS.departement
    , TCS.category
    , m.mnth
ORDER BY category ASC
    , CURR_MONTH DESC;

nb: I prefer to use (sql standard) COALESCE instead of (the properietary) ISNULL and I think the second case expression can be simplified to be similar in structure to the first, but it returns 0 if true otherwise QTY.