Hi all this is the query i am using
SELECT
m.solversionid,
m.variant,
m.description,
m.level,
m.services_crmid,
m.parentnode,
m.itemtype,
CASE
WHEN m.level = 1 THEN
COALESCE((
SELECT SUM(sub.sspmaxmargin)
FROM CV_FIN_FINAL_MIGRATION sub
WHERE sub.parentnode = m.parentnode AND sub.level = 2
), 0)
WHEN m.level = 0 AND m.description != 'Total' THEN
COALESCE((
SELECT SUM(sub.sspmaxmargin)
FROM CV_FIN_FINAL_MIGRATION sub
WHERE sub.parentnode = m.parentnode AND sub.level = 1
), 0)
WHEN m.level = 0 AND m.description = 'Total' THEN
COALESCE((
SELECT SUM(sub.sspmaxmargin)
FROM CV_FIN_FINAL_MIGRATION sub
WHERE sub.parentnode = m.parentnode
), 0)
ELSE
m.sspmaxmargin
END AS sspmaxmargin
FROM CV_FIN_FINAL_MIGRATION m
WHERE m.solversionid = '2371791525-055' AND m.variant = '0002'
ORDER BY m.level;
and the result i am getting is below i am seeing all 0 values for level 1 and 0 i should get sum of level 2 in level 1 and sum of level 1 in level 0
