데이터베이스 질문과 답변

그룹의 max값을 가진 row의 다른 데이터를 보여주고싶은데 ㅠ 2 3 1,681

by ZeTT [SQL Query] [2014.10.15 08:22:38] 

WITH T AS (
SELECT 'A60' AS SITE, 'AAA' AS ITEMS, 100 AS AMT FROM DUAL UNION ALL
SELECT 'A60' AS SITE, 'BBB' AS ITEMS, 10 AS AMT FROM DUAL UNION ALL
SELECT 'A60' AS SITE, 'CCC' AS ITEMS, 30 AS AMT FROM DUAL UNION ALL
SELECT 'A60' AS SITE, 'DDD' AS ITEMS, 40 AS AMT FROM DUAL UNION ALL
SELECT 'A90' AS SITE, 'EEE' AS ITEMS, 40 AS AMT FROM DUAL UNION ALL
SELECT 'A90' AS SITE, 'FFF' AS ITEMS, 50 AS AMT FROM DUAL
)
SELECT * FROM T;

 

원하는 결과 값

SITE SUM(AMT)  최대값(AMT)의 ITEMS 컬럼

A60   180         AAA

A90   90           FFF

 

A60 에 MAX(AMT) 값이 100

A90 에 MAX(AMT) 값이 50 각각 이렇게 나오는데요

MAX(AMT)에 해당하는 ITEMS 가져오려면 어떻게 해야 되는지 문의드려요 ㅠㅠ

지금은 이렇게 간단한 소스이긴한데..

제가 실제로 반영하려는 쿼리는 어마무시해서요...; 잘부탁드립니당 :)

by dutch5 [2014.10.15 09:06:56] 
WITH T AS (
SELECT 'A60' AS SITE, 'AAA' AS ITEMS, 100 AS AMT FROM DUAL UNION ALL
SELECT 'A60' AS SITE, 'BBB' AS ITEMS, 10 AS AMT FROM DUAL UNION ALL
SELECT 'A60' AS SITE, 'CCC' AS ITEMS, 30 AS AMT FROM DUAL UNION ALL
SELECT 'A60' AS SITE, 'DDD' AS ITEMS, 40 AS AMT FROM DUAL UNION ALL
SELECT 'A90' AS SITE, 'EEE' AS ITEMS, 40 AS AMT FROM DUAL UNION ALL
SELECT 'A90' AS SITE, 'FFF' AS ITEMS, 50 AS AMT FROM DUAL
)
select sum(amt) sum_amt, max(items) keep (dense_rank first order by amt desc) as max_site
from t
group by site

이렇게 하시면 됩니다.

 

by ZeTT [2014.10.15 09:10:04]

와우 ...MAX KEEP 이라는게 있었네요! 

OVER 만 알고있었는데 ㅎㅎ

덕분에 많이 알고갑니다 고맙습니다!!

:) 

by 날도 [2014.10.15 09:16:49] 
WITH T AS (
SELECT 'A60' AS SITE, 'AAA' AS ITEMS, 100 AS AMT FROM DUAL UNION ALL
SELECT 'A60' AS SITE, 'BBB' AS ITEMS, 10 AS AMT FROM DUAL UNION ALL
SELECT 'A60' AS SITE, 'CCC' AS ITEMS, 30 AS AMT FROM DUAL UNION ALL
SELECT 'A60' AS SITE, 'DDD' AS ITEMS, 40 AS AMT FROM DUAL UNION ALL
SELECT 'A90' AS SITE, 'EEE' AS ITEMS, 40 AS AMT FROM DUAL UNION ALL
SELECT 'A90' AS SITE, 'FFF' AS ITEMS, 50 AS AMT FROM DUAL
)
select site,
       sum(amt),
       max(items)
from
(
    SELECT site,    
           amt,
           first_value(items) over (partition by site order by amt desc) items
    FROM T
)
group by site
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입