postgresql 집계함수 사용

SELECT *
,SUM(CASE WHEN pymt_stlv1_cd = '합계' OR cid = '소계' THEN 0 ELSE SS.TX_AMT end) OVER(PARTITION BY pymt_stlv1_cd) AS sub_sum
,SUM(CASE WHEN pymt_stlv1_cd = '합계' OR cid = '소계' THEN 0 ELSE SS.TX_AMT end) OVER() AS total_sum
FROM (
SELECT CASE WHEN GROUPING(P.PYMT_STLV1_CD) = 1 THEN '합계' ELSE p.pymt_stlv1_cd END AS pymt_stlv1_cd
,CASE WHEN GROUPING(ss.cid) = 1 THEN '소계' ELSE SS.CID END AS cid
,SUM(ss.tx_amt) AS tx_amt
FROM SPSDB.TB_SLS_STST SS
JOIN SPPDB.TB_PYMT P ON SS.PYMT_CD = P.PYMT_CD
WHERE SS.ORG_TX_YMD BETWEEN '2024-03-01'::DATE AND '2024-03-31'::DATE
GROUP BY ROLLUP(P.PYMT_STLV1_CD, SS.CID)
) ss
;