GROUP BY

GROUP BY 미사용

SELECT CountryRegion
FROM Customer;

GROUP BY 사용

SELECT CountryRegion
FROM Customer
GROUP BY CountryRegion;

멀티 GROUP BY

SELECT CountryRegion,City
FROM Customer
GROUP BY CountryRegion, City;

먼저 리전 GROUP BY 한 다음 city GROUP BY

GROUP BY & 집계함수

해당 국가의 고객수 COUNT

SELECT CountryRegion, 
COUNT(*)
AS Customers_by_Country 
FROM Customer
GROUP BY CountryRegion;

해당 국가의 고객수 COUNT

주문별 주문금액 합계

SELECT SalesOrderID, 
SUM(SubTotal)
AS Order_Total 
FROM SalesOrderDetail
GROUP BY SalesOrderID;

2008년 7월 일별 주문 합계 구하기

/* 2008년 7월의 일별 주문 합계 구하기*/

SELECT *
FROM SalesOrder so
INNER JOIN SalesOrderDetail sod
ON (so.SalesOrderID = sod.SalesOrderID)
WHERE 
so.OrderDate BETWEEN '2008-07-01' and '2008-07-31';

날짜 별로 GROUP BY

/* 2008년 7월의 일별 주문 합계 구하기*/

SELECT so.OrderDate,SUM(sod.subtotal) as total
FROM SalesOrder so
INNER JOIN SalesOrderDetail sod
ON (so.SalesOrderID = sod.SalesOrderID)
WHERE 
so.OrderDate BETWEEN '2008-07-01' and '2008-07-31'
GROUP BY so.OrderDate;

GROUP BY HAVING

2008년 7월의 일별 주문 중에서총 금액이 3,000 이상인 것

/* 2008년 7월의 일별 주문 중에서
총 금액이 3,000 이상인 것*/

SELECT so.OrderDate,SUM(sod.subtotal) as total
FROM SalesOrder so
INNER JOIN SalesOrderDetail sod
ON (so.SalesOrderID = sod.SalesOrderID)
WHERE 
so.OrderDate BETWEEN '2008-07-01' and '2008-07-31'
GROUP BY so.OrderDate
HAVING SUM(sod.SubTotal) >= 3000;

GROUP BY & ORDER BY

방법1

/* 2008년 7월의 일별 주문 중에서
총 금액이 3,000 이상인 것*/

SELECT so.OrderDate,SUM(sod.subtotal) as total
FROM SalesOrder so
INNER JOIN SalesOrderDetail sod
ON (so.SalesOrderID = sod.SalesOrderID)
WHERE 
so.OrderDate BETWEEN '2008-07-01' and '2008-07-31'
GROUP BY so.OrderDate
HAVING SUM(sod.SubTotal) >= 3000
order by total;

방법2

/* 2008년 7월의 일별 주문 중에서
총 금액이 3,000 이상인 것*/

SELECT so.OrderDate,SUM(sod.subtotal) as total
FROM SalesOrder so
INNER JOIN SalesOrderDetail sod
ON (so.SalesOrderID = sod.SalesOrderID)
WHERE 
so.OrderDate BETWEEN '2008-07-01' and '2008-07-31'
GROUP BY so.OrderDate
HAVING SUM(sod.SubTotal) >= 3000
order by SUM(sod.SubTotal);

Point