[MySQL] GROUP BY / HAVING

---

GROUP BY / HAVING

 

예제 1) 

# 주문상태별 총금액 조회
SELECT 
    o.status
    , SUM(od.quantityOrdered * od.priceEach) AS amount
FROM
    orders o
JOIN orderdetails od 
    USING (orderNumber)
GROUP BY 
    o.status;

status    |amount    |
----------+----------+
Shipped   |8865094.64|
Resolved  | 134235.88|
Cancelled | 238854.18|
On Hold   | 169575.61|
Disputed  |  61158.78|
In Process| 135271.52|

 

예제 2)

# 발송된 주문들의 연도별 총금액 조회
SELECT 
    YEAR(o.orderDate) AS year,
    SUM(od.quantityOrdered * od.priceEach) AS amount
FROM
    orders o
JOIN orderdetails od 
    USING (orderNumber)
WHERE
    o.status = 'Shipped'
GROUP BY 
    YEAR(o.orderDate);

year|amount    |
----+----------+
2003|3223095.80|
2004|4300602.99|
2005|1341395.85|

 

예제 3) GROUP BY 절로 반환된 행들을 필터링하려면 HAVING 절 사용

# 2003년 후에 발송된 주문들의 연도별 총금액 조회
SELECT 
    YEAR(o.orderDate) AS year,
    SUM(od.quantityOrdered * od.priceEach) AS amount
FROM
    orders o
JOIN orderdetails od 
    USING (orderNumber)
WHERE
    o.status = 'Shipped'
GROUP BY 
    year
HAVING 
    year > 2003;

# SQL 표준은 GROUP BY 절에 별칭 사용불가
# MySQL 에서는 GROUP BY 절에 별칭 사용가능

year|amount    |
----+----------+
2004|4300602.99|
2005|1341395.85|