it 수업 6주차 (12) Group By 절

 

※ group by로 묶어줄 땐 group by 뒤에 들어오는 열의 순서가 중요하다. 순서가 바뀌면 출력되는 표도 바뀌게 된다.

묶어준 열은 나타낼 표에도 적어줘야한다. 

즉, 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.

 또한 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다.

ex) select 열이름1, 열이름2, 집계함수 from 테이블명 group by 열이름1, 열이름2;

 

 

● 총합 구하기

 

sum(열이름) 하면 총합을 구할 수 있다.

 

select userid, sum(amount) as '구매총량' from buytbl group by userid;

 

 

 

● 평균 구하기

 

avg(열이름)하면 평균 값을 구할 수 있고

truncate(값, 소숫점자리) 하면 소숫점 자리만큼만 출력 할 수 있다.

 

select userid, avg(amount) as '구매평균값' from buytbl group by userid;

 

select userid, truncate(avg(amount*price),2) as '구매평균액' from buytbl group by userid;

 

 

 

● 최댓값, 최솟값 출력

 

max(열이름) 하면 최댓값을 구할 수 있고

min(열이름) 하면 최솟값을 구할 수 있다.

 

 

select max(height) from usertbl;

 

select min(height) from usertbl;

 

select * from usertbl where height=(select max(height) from usertbl) or height=(select min(height) from usertbl);

 

 

 

※ 연습 문제

 

1. select userid, sum(amount) from buytbl group by userid;

 

2. select truncate(avg(height),2) from usertbl;

 

3. select userid,amount from buytbl where amount=(select max(amount) from buytbl) or amount=(select min(amount) from buytbl);

 

4. select count(groupname) from buytbl;

 

 

 

 

1. select city, avg(creditlimit) from customers group by city;

 

2. select orderNumber, sum(quantityOrdered) from orderdetails group by ordernumber;

 

3.  select productVendor, sum(quantityinstock) from products group by productVendor;

 

 

 

 

 

※ Group By + Having (Group By에 조건을 붙일 때 사용)

   WHERE절 에서는 집계함수를 사용 할 수 없기 때문에 HAVING 절을 사용해 집계함수(조건)을 사용할 수 있다.

 

 

select userid, sum(amount) as '구매총량' from buytbl group by userid having sum(amount)>5;

 

select userid, sum(amount) as '구매총량' from buytbl group by userid having '구매총량'>5; 이렇게 하면 값이 출력되지 않는다.

 

select userid, truncate(avg(amount*price),2) as '구매평균액' from buytbl group by userid having truncate(avg(amount*price),2) > 50;

 

 

 

※ Group By + Rollup : 부분합과 총합 구하기

 

 

select num, groupname, sum(price*amount) from buytbl group by groupname, num with rollup;  하면 먼저 그룹화시킨 groupname으로 먼저 정렬시키고 부분합들을 나타낸뒤 한 groupname이 끝나면 그 groupname의 총합이 출력된다.

 

select groupname, sum(price*amount) from buytbl group by groupname with rollup; 는 총합만 구하는 것이다.

 

select userid, addr, avg(height) from usertbl group by addr,userid with rollup; 이것은 지역별로 키의 평균을 구하고 전체 평균을 구하는 것이다.

 

 

 

 

※ 연습 문제

 

1. select userid, prodname, sum(price*amount) from buytbl group by prodname, userid;

 

2. select userid, prodname, sum(price*amount) from buytbl group by prodname, userid having sum(price*amount) >= 1000;

 

3. select distinct userid, prodname, price from buytbl where price=(select max(price) from buytbl) or price=(select min(price) from buytbl);

 

4. select * from buytbl where groupname is not null;

 

5. select prodname,sum(price*amount) from buytbl group by prodname with rollup;