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.
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";