SQL group and condition sum in the last row

I have several items which belongs to different group, some are unknown group. I need the sum of each group by date. I just try below SQL but still not exactly what I want:

with A as (
SELECT PG, EMANAGER_DATE,
  CASE WHEN (EMANAGER_DATE = '2021-09') THEN SUM(NET_VALUE_USD) ELSE 0 END AS "2021-09",
  CASE WHEN (EMANAGER_DATE = '2021-10') THEN SUM(NET_VALUE_USD) ELSE 0 END AS "2021-10",
  CASE WHEN (EMANAGER_DATE = '2021-11') THEN SUM(NET_VALUE_USD) ELSE 0 END AS "2021-11",
  CASE WHEN (EMANAGER_DATE = '2021-12') THEN SUM(NET_VALUE_USD) ELSE 0 END AS "2021-12"
  FROM table1
  GROUP BY PG, EMANAGER_DATE
)
select PG, max("2021-09") AS "2021-09", max("2021-10") AS "2021-10", max("2021-11") AS "2021-11", max("2021-12") AS "2021-12" from A GROUP BY PG

DB: SAP HANA

Data:

Item	PG	NET_VALUE_USD	EMANAGER_DATE
1	1	100	2021-09
2	1	200	2021-10
3	2	300	2021-09
4	2	400	2021-09
5	3	500	2021-09
6	3	200	2021-11
7	3	300	2021-11
8		900	2021-09

Expected result:

PG	2021-09	2021-10	2021-11
?	900	0	0
1	100	200	0
2	700	0	0
3	500	0	500
total	2200	200	500

Answer

You can adjust your query and use GROUPING SETS:

SELECT (CASE WHEN GROUPING(PG) = 1 THEN 'Total' ELSE PG END) as PG,
       SUM(CASE WHEN EMANAGER_DATE = '2021-09' THEN NET_VALUE_USD ELSE 0 END) AS "2021-09",
       SUM(CASE WHEN EMANAGER_DATE = '2021-10' THEN NET_VALUE_USD ELSE 0 END) AS "2021-10",
       SUM(CASE WHEN EMANAGER_DATE = '2021-11' THEN NET_VALUE_USD ELSE 0 END) AS "2021-11",
       SUM(CASE WHEN EMANAGER_DATE = '2021-12' THEN NET_VALUE_USD ELSE 0 END) AS "2021-12"
FROM _SYS_BIC."APP_SCM/ZCV_APP_SCM_CTOS_PARENT_UNCONFIRMED_BACKLOG_P"
GROUP BY GROUPING SETS ( (PG), () );