3. 기본 SELECT문(GROUP BY HAVING, COUNT)

# buy 테이블 목록

SELECT * FROM buy

# GROUP BY

SELECT mem_id "회원 아이디" , SUM(amount) "총 구매 개수" FROM buy GROUP BY mem_id;

=> buy 테이블의 mem_id 별로 묶인 mem_id 열과 amount열의 합을 보여준다.

---

-- GROUP BY , 총 구매 금액 표현
SELECT mem_id "회원 아이디", SUM(amount*price) "총 구매 금액" FROM buy GROUP BY mem_id;

---

SELECT mem_id "회원아이디", AVG(amount) "평균 구매 개수" FROM buy GROUP BY mem_id;

---

# GROUP BY HAVING

"GROUP BY WHERE" => Error !!

-- ERROR 발생 Error Code: 1111. Invalid use of group function
SELECT mem_id "회원 아이디", SUM(price*amount) FROM buy WHERE SUM(price*amount) > 1000 GROUP BY mem_id;

 "GROUP BY에서는 HAVING을 사용해야 한다."

SELECT mem_id "회원 아이디", SUM(price*amount) FROM buy Group by mem_id HAVING SUM(price*amount) >1000;

ORDER BY로 내림차순 정렬까지

SELECT mem_id "회원 아이디", SUM(price*amount) FROM buy Group by mem_id HAVING SUM(price*amount) >1000 ORDER BY SUM(price*amount) DESC;

---

# COUNT

SELECT * FROM member

- 전체 카운팅

SELECT COUNT(*) FROM member;

- phone1 열에서의 null 값 제외하고 카운트(count)

SELECT COUNT(phone1) "연락처가 있는 회원" FROM member;