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

프로그래밍/Oracle

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

도깨비방망이 2018. 12. 5. 16:18

[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

저작자표시 비영리 변경금지