[Oracle] 각 그룹별 SUM 및 특정 컬럼 첫번째 Row 가져오기

[Oracle] 각 그룹별 SUM 및 특정 컬럼 첫번째 Row 가져오기

WITH TEMP AS ( SELECT 'A' AS "품목1", 'A1-1' AS "품목2", 100 AS "품목 당 목표매출량", 20 AS "매출량" FROM DUAL UNION ALL SELECT 'A' AS "품목1", 'A1-1' AS "품목2", 100 AS "품목 당 목표매출량", 40 AS "매출량" FROM DUAL UNION ALL SELECT 'A' AS "품목1", 'A1-2' AS "품목2", 200 AS "품목 당 목표매출량", 20 AS "매출량" FROM DUAL UNION ALL SELECT 'A' AS "품목1", 'A1-2' AS "품목2", 200 AS "품목 당 목표매출량", 40 AS "매출량" FROM DUAL UNION ALL SELECT 'B' AS "품목1", 'B1-1' AS "품목2", 200 AS "품목 당 목표매출량", 50 AS "매출량" FROM DUAL UNION ALL SELECT 'B' AS "품목1", 'B1-1' AS "품목2", 200 AS "품목 당 목표매출량", 150 AS "매출량" FROM DUAL UNION ALL SELECT 'B' AS "품목1", 'B1-2' AS "품목2", 300 AS "품목 당 목표매출량", 10 AS "매출량" FROM DUAL UNION ALL SELECT 'B' AS "품목1", 'B1-2' AS "품목2", 300 AS "품목 당 목표매출량", 20 AS "매출량" FROM DUAL ) SELECT T."품목1", SUM(CASE WHEN ORDER_SEQ = 1 THEN "품목 당 목표매출량" END) "목표량", SUM("매출량") "매출량" FROM ( SELECT "품목1","품목2",ROW_NUMBER() OVER (PARTITION BY "품목1","품목2" ORDER BY "품목1","품목2" ASC) AS ORDER_SEQ, "품목 당 목표매출량", "매출량" FROM TEMP ) T GROUP BY T."품목1"

품목     목표량        매출량

- ---------- ----------

A        300        120

B        500        230