퍼온곳 : http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=294&page=1&position=1#294
글쓴이 : 김홍선
문제)
What's the proper way to query for a total for a value and also the total for all values for a particular set of parameters?
For example, I have a table of orders that customer service reps make. The reps place multiple orders per day for various products. I'm trying to pull a report that displays the customer service ID (csID), total number of a particular product sold, and the total of all products sold... within a date range.
Sample Orders Table (heavily snipped):
ORDER_ID CS_ID PRO_ID QTY_SOLD DATE
-------- ----- ------ -------- ---------
1 10 105 3 06-JUL-08
2 12 105 4 06-JUL-10
3 10 105 3 06-JUL-10
3 10 120 2 06-JUL-10
4 12 105 1 06-JUL-11
When querying for the totals between July 8-July 11, the query should return something like:
CS_ID PRO_ID PRO_TOTAL ALL_TOTAL
----- ------ --------- ---------
10 105 6 8
10 120 2 8
12 105 5 5
I've tried various queries that work when I sum up individually, but when I include 2 sums for a row I'm getting duplicates and the sums are too high.
This seems like it would be fairly straightforward but apparently I'm overlooking a key item.
답변)
테이블 생성 스크립트와 데이타는 아래와 같이 주어진다.
CREATE TABLE orders
(order_id NUMBER,
cs_id NUMBER,
pro_id NUMBER,
qty_sold NUMBER,
dt DATE)
INSERT INTO orders
VALUES (1, 10, 105, 3, '06-JUL-08')
INSERT INTO orders
VALUES (2, 12, 105, 4, '06-JUL-08')
INSERT INTO orders
VALUES (3, 10, 105, 3, '06-JUL-10')
INSERT INTO orders
VALUES (3, 10, 120, 2, '06-JUL-10')
INSERT INTO orders
VALUES (4, 12, 105, 1, '06-JUL-11')
group by를 하는 컬럼의 범위(갯수)가 다를 경우 sum을 구하는 것이 관건인 문제이다.
sum 함수가 그룹함수와 분석함수로 중첩되어 사용되는 다음의 답변 쿼리를 자세히 살펴보자.
SELECT cs_id
, pro_id
, SUM (qty_sold) pro_total
, SUM (SUM (qty_sold)) OVER (PARTITION BY cs_id) all_total
FROM orders
WHERE dt >= TO_DATE ('06-JUL-08', 'YY-MON-DD')
AND dt < TO_DATE ('06-JUL-11', 'YY-MON-DD') + 1
GROUP BY cs_id
, pro_id
원문은 아래 페이지를 참고한다.
http://www.orafaq.com/forum/t/66310/78939/