꿈꾸는 개발자, DBA 커뮤니티 구루비

롤업관련 드립니다.

by 클라나드 [2014.06.24 20:55:49]

WITH T AS
( 
  SELECT '해태' AS BRAND, '과자' AS CATE, '감자칩' AS NAME, '100' AS PRICE FROM DUAL UNION ALL
  SELECT '해태' AS BRAND, '하드' AS CATE, '빙수바' AS NAME, '120' AS PRICE FROM DUAL UNION ALL
  SELECT '해태' AS BRAND, '음료' AS CATE, '콜라'   AS NAME, '150' AS PRICE FROM DUAL UNION ALL
  SELECT '롯데' AS BRAND, '과자' AS CATE, '포카칩' AS NAME, '200' AS PRICE FROM DUAL UNION ALL
  SELECT '롯데' AS BRAND, '하드' AS CATE, '빵빠레' AS NAME, '210' AS PRICE FROM DUAL UNION ALL
  SELECT '롯데' AS BRAND, '음료' AS CATE, '포카리' AS NAME, '230' AS PRICE FROM DUAL UNION ALL 
  SELECT '농심' AS BRAND, '과자' AS CATE, '강과자' AS NAME, '250' AS PRICE FROM DUAL UNION ALL
  SELECT '농심' AS BRAND, '하드' AS CATE, '하드당' AS NAME, '220' AS PRICE FROM DUAL UNION ALL  
  SELECT '농심' AS BRAND, '음료' AS CATE, '음료임' AS NAME, '350' AS PRICE FROM DUAL UNION ALL  
  SELECT '해태' AS BRAND, '과자' AS CATE, '양파깡' AS NAME, '190' AS PRICE FROM DUAL UNION ALL
  SELECT '해태' AS BRAND, '하드' AS CATE, '시원바' AS NAME, '170' AS PRICE FROM DUAL UNION ALL
  SELECT '해태' AS BRAND, '음료' AS CATE, '과자용' AS NAME, '140' AS PRICE FROM DUAL UNION ALL
  SELECT '롯데' AS BRAND, '과자' AS CATE, '까까'   AS NAME, '240' AS PRICE FROM DUAL UNION ALL
  SELECT '롯데' AS BRAND, '하드' AS CATE, '보석바' AS NAME, '210' AS PRICE FROM DUAL UNION ALL
  SELECT '롯데' AS BRAND, '음료' AS CATE, '사이다' AS NAME, '220' AS PRICE FROM DUAL UNION ALL 
  SELECT '농심' AS BRAND, '과자' AS CATE, '사탕과자' AS NAME, '250' AS PRICE FROM DUAL UNION ALL
  SELECT '농심' AS BRAND, '하드' AS CATE, '누가바' AS NAME, '210' AS PRICE FROM DUAL UNION ALL  
  SELECT '농심' AS BRAND, '음료' AS CATE, '시원함' AS NAME, '370' AS PRICE FROM DUAL  
)
SELECT * 
FROM   T
ORDER BY BRAND, CATE

위의 쿼리의 결과갑은

농심   과자   강과자   250
농심   과자   사탕과자   250
농심   음료   음료임   350
농심   음료   시원함   370
농심   하드   하드당   220
농심   하드   누가바   210
롯데   과자   포카칩   200
롯데   과자   까까   240
롯데   음료   포카리   230
롯데   음료   사이다   220
롯데   하드   빵빠레   210
롯데   하드   보석바   210
해태   과자   감자칩   100
해태   과자   양파깡   190
해태   음료   콜라   150
해태   음료   과자용   140
해태   하드   빙수바   120
해태   하드   시원바   170

입니다.

제가 하려고 하는겄은 각각 브랜드마다 계 각각 카테고리 마다 계입니다

원하는 결과값은

농심 과자 강과자 250
농심 과자 사탕과자 250

과자계 500
농심 음료 음료임 350
농심 음료 시원함 370

음료계 720
농심 하드 하드당 220
농심 하드 누가바 210

하드계 430

농심계 1650
롯데 과자 포카칩 200
롯데 과자 까까 240

과자계 440
롯데 음료 포카리 230
롯데 음료 사이다 220

음료계 450
롯데 하드 빵빠레 210
롯데 하드 보석바 210

하드계 420

롯데계 1310
해태 과자 감자칩 100
해태 과자 양파깡 190

과자계 290
해태 음료 콜라 150
해태 음료 과자용 140

음료계 290
해태 하드 빙수바 120
해태 하드 시원바 170

하드계 290

해태계 670

총합계 7260

입니다.

고수님들의 도움을 부탁드립니다.

많은 공부가 될겄같습니다.

행복하세요

by 필상 [2014.06.25 09:02:52]

SELECT CASE GROUPING_ID(BRAND, CATE, NAME)
            WHEN 7 THEN '총합계'
            WHEN 3 THEN BRAND || '계' ELSE BRAND END AS BRAND,
       CASE GROUPING_ID(BRAND, CATE, NAME) WHEN 1 THEN CATE || '계' ELSE CATE END AS CATE,
       NAME, SUM(PRICE),
       CASE WHEN GROUPING_ID(BRAND, CATE, NAME) = 7 THEN 1 ELSE 0 END AS SEQ
FROM T
GROUP BY ROLLUP(BRAND, CATE, (NAME, PRICE))
ORDER BY SEQ, BRAND, CATE

by 호이호이 [2014.06.25 09:30:59]

select decode(grouping(cate),1,decode(grouping(brand),1,'총합계',0,brand||'계'),0,brand) brand
     , decode(grouping(name),1,decode(grouping(cate),1,'',0,cate||'계'),0,cate) cate
     , name
     , sum(price)
from t
group by rollup (brand,cate, name)

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변