Joins with grouping
Reports: joins with grouping
The real power comes from combining what you have learned: join two tables, group the joined rows, and summarise each group with an aggregate.
A common report is "how much has each customer spent?" — join customers to their orders, group by customer, and SUM the totals.
리포트: 그룹화Join
진정한 힘은 배운 것을 결합하는 데 있습니다: join 두 테이블, group 결합된 행, 그리고 summarise 각 집합을 집계 함수로.
일반적인 리포트는 "각 고객이 얼마나 지출했는가?"입니다 — 고객을 주문에 join하고, 고객별로 group하며, SUM 총액을 계산합니다.
One row per customer
Reading it in order: join the tables, group the rows by customer, then for each group count the orders and add up the totals.
고객별 한 줄
SELECT customer.name,
COUNT(*) AS orders,
ROUND(SUM(orders.total), 2) AS spent
FROM customer
INNER JOIN orders ON customer.id = orders.customer_id
GROUP BY customer.id
ORDER BY customer.name;
순서대로 읽기: 테이블을 join하고, 행을 고객별로 group한 후, 각 집합에 대해 주문 개수를 세고 총액을 합산합니다.
Common mistakes
- Join first, then
GROUP BYto summarise the joined rows. - Qualify a column with its table name when both tables share it.
흔한 실수
- 먼저 join하고, 결합된 행을 요약하기 위해
GROUP BY를 수행합니다. - 두 테이블이 열을 공유할 경우 열에 테이블 이름을 붙여 식별합니다.
Joining tables
An inner join drops rows that have no match.
For each customer who has orders, show their name, their number of orders as orders, and their total spend (2 d.p.) as spent. Join, group by customer.id, and order by name.
Click Run to see the output here. · 출력을 보려면 '실행'을 클릭하세요.
An INNER JOIN hid Ben and Dan, who have no orders. Use a LEFT JOIN to keep every customer, and COUNT(orders.id) so a customer with no orders shows 0. Order by name.
Click Run to see the output here. · 출력을 보려면 '실행'을 클릭하세요.