Need to display 0 with Column value name if the column value in 0 or null in snowflake

Question

ex - I have a column called column1. It has multiple values, like 15 or more values in it. I need to pick only four values (AAA,BBB,CCC,DDD alone) in that for my calculation. These four values may be null or 0 and, due to that, I get the sum of these as 0 or null.

I need to display all the four values in the query output if the sum is 0 or more ans independent row. If the sum is 0 then it should show 0 in the count column.

Further, I need to rename the Column values and group them into a single value.Since, there might be other values which contain these values in a different format.

My query :Sample code for understanding

select ADD_MONTHS(LAST_DAY(select  date  from cw.cw_cs.cw_vw_cw_us_date),-1) AS Date,
'MT09' as "ID",
'Number of Asset' as "Name",
(case when Entity Like'%AAA%' then 'AAA'
         when Entity like '%BBB%' then 'Sol-BBB'
        when Entity like '%CCC%' then 'MU-CCC'
         when Entity = 'DDD' then 'MU-DDD'
         else 'Others' end ) as "Entity",
COUNT(DISTINCT CASE WHEN SEVERITY_ID='X' THEN CMDBHASH_ASSET_CVE_COMBINE END)  as Numerator
FROM  cw.cw_cns.CW_VW_MS_VN_SU_HIS
group by "Entity"

output received:

DATE        Metric ID   Metric Name Legal Entity    NUMERATOR
7/31/2024   MT09    Number of Asset   MU-CCC    5
7/31/2024   MT09    Number of Asset   Others    0

My need :

DATE    Metric ID   Metric Name Legal Entity    NUMERATOR
7/31/2024   MT09    Number of Asset AAA          0
7/31/2024   MT09    Number of Asset MU-CCC           5
7/31/2024   MT09    Number of Asset Sol-BBB          0
7/31/2024   MT09    Number of Asset MU-DDD           0
7/31/2024   MT09    Number of Asset Others           2

Can someone please help me to achieve this.

Answer 1

You can try this may be

-Generate all required entities with zero counts using a CTE.

-Perform a LEFT JOIN between this CTE and the actual aggregated data to ensure all specified entities are included in the final output.

I don't have a sample dataset to test, but you can try the below query:

Example:

    WITH RequiredEntities AS (
    SELECT 'AAA' AS Entity
    UNION ALL
    SELECT 'Sol-BBB'
    UNION ALL
    SELECT 'MU-CCC'
    UNION ALL
    SELECT 'MU-DDD'
),
AggregatedData AS (
    SELECT 
        ADD_MONTHS(LAST_DAY(date), -1) AS Date,
        'MT09' AS "Metric ID",
        'Number of Asset' AS "Metric Name",
        CASE 
            WHEN Entity LIKE '%AAA%' THEN 'AAA'
            WHEN Entity LIKE '%BBB%' THEN 'Sol-BBB'
            WHEN Entity LIKE '%CCC%' THEN 'MU-CCC'
            WHEN Entity = 'DDD' THEN 'MU-DDD'
            ELSE 'Others'
        END AS "Legal Entity",
        COUNT(DISTINCT CASE WHEN SEVERITY_ID='X' THEN CMDBHASH_ASSET_CVE_COMBINE END) AS Numerator
    FROM cw.cw_cns.CW_VW_MS_VN_SU_HIS
    GROUP BY 
        CASE 
            WHEN Entity LIKE '%AAA%' THEN 'AAA'
            WHEN Entity LIKE '%BBB%' THEN 'Sol-BBB'
            WHEN Entity LIKE '%CCC%' THEN 'MU-CCC'
            WHEN Entity = 'DDD' THEN 'MU-DDD'
            ELSE 'Others'
        END
)
SELECT 
    ADD_MONTHS(LAST_DAY(CURRENT_DATE), -1) AS Date, 
    'MT09' AS "Metric ID", 
    'Number of Asset' AS "Metric Name", 
    RE.Entity AS "Legal Entity", 
    COALESCE(A.Numerator, 0) AS Numerator
FROM RequiredEntities RE
LEFT JOIN AggregatedData A ON RE.Entity = A."Legal Entity"
UNION
SELECT 
    ADD_MONTHS(LAST_DAY(CURRENT_DATE), -1) AS Date, 
    'MT09' AS "Metric ID", 
    'Number of Asset' AS "Metric Name", 
    "Legal Entity", 
    Numerator
FROM AggregatedData
WHERE "Legal Entity" NOT IN ('AAA', 'Sol-BBB', 'MU-CCC', 'MU-DDD')
ORDER BY 
    "Legal Entity";