SELECT A.STAFF_ID,
B.STAFF_ID,
B.FIRST_NAME,
B.LAST_NAME,
COUNT(A.PAYMENT_ID) AS COUNT
FROM PAYMENT A,
STAFF B
WHERE A.STAFF_ID = B.STAFF_ID
GROUP BY A.STAFF_ID,
B.STAFF_ID;
SELECT CUSTOMER_ID,
SUM(AMOUNT) AS AMOUNT
FROM PAYMENT
GROUP BY CUSTOMER_ID
HAVING SUM(AMOUNT) > 200
ORDER BY AMOUNT DESC;
-- having절은 group by
SELECT A.CUSTOMER_ID ,
B.EMAIL ,
SUM(A.AMOUNT) AS AMOUNT
FROM PAYMENT A,
CUSTOMER B
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID
GROUP BY A.CUSTOMER_ID,
B.EMAIL
HAVING SUM(A.AMOUNT) > 200
SELECT BRAND,
SEGMENT,
SUM(QUANTITY)
FROM SALES
GROUP BY GROUPING SETS((BRAND,SEGMENT), (BRAND), (SEGMENT), ());
-- union all 로 활용할 경우엔 같은 테이블을 4번 조회해서 union all해야됨
SELECT GROUPING(BRAND) GROUPING_BRAND,
GROUPING(SEGMENT) GROUPING_SEGMENT,
BRAND SEGMENT,
SUM(QUANTITY)
FROM SALES
GROUP BY GROUPING SETS((BRAND,SEGMENT), (BRAND), (SEGMENT), ());
GROUPING() : 해당 컬럼이 집계에 사용되었으면 0, 그렇지 않으면 1을 리턴.
SELECT BRAND,
SEGMENT,
SUM(QUANTITY)
FROM SALES
GROUP BY ROLLUP(BRAND, SEGMENT)
ORDER BY BRAND, SEGMENT
-- group by + rollup절에 맨앞에 쓴 컬럼 기준의 합계도 나오고 + 전체 합계도 나왔다
SELECT BRAND,
SEGMENT,
SUM(QUANTITY)
FROM SALES
GROUP BY SEGMENT,
ROLLUP (BRAND)
ORDER BY BRAND,
SEGMENT
-- 부분 rollup시 전체 합게는 구하지 않는다
SELECT BRAND,
SEGMENT,
SUM(QUANTITY)
FROM SALES
GROUP BY CUBE(BRAND, SEGMENT)
ORDER BY BRAND,
SEGMENT
-- cube = group by 절 합께 + brands별 + segment별 + 전체합게
SELECT BRAND,
SEGMENT,
SUM(QUANTITY)
FROM SALES
GROUP BY BRAND,
CUBE(SEGMENT)
ORDER BY BRAND,
SEGMENT