[MySQL] count 조건 및 방법 (예시)

안녕하세요.

오늘은 MySQL로 count를 사용하는 다양한 방법들에 대해 알아보겠습니다.

 

 MySQL Count 방법들

▣ 예시 데이터 : orders

 

1) GROUP BY

- 단순한 GROPU BY 절을 통해서 member_id별로 주문 횟수를 알아보기. (내림차순으로 정렬)

SELECT member_id, count(*) as member_count
FROM orders GROUP BY member_id
ORDER BY member_count desc

member_id	member_count
11890	11
49549	7
85945	5
192368	5
173537	4
70955	3
364892	3
205394	2
34860	2
161249	2
218095	2
145132	2
216440	1
258721	1
108191	1
201712	1
409520	1
285071	1
219228	1
302700	1

 

2) COUNT 함수 내 조건설정

- pay_yn은 주문했으나 실제 결제로 이어진 지 여부를 확인할 수 있는 컬럼임.

- count 내 조건 설정을 통해 member_id별 주문 실결제 횟수를 알아보기.

SELECT member_id, 
	   count(case when pay_yn = 'y' then 1 end) as pay_count,
       count(case when pay_yn = 'n' then 1 end) as no_pay_count
FROM orders
GROUP BY member_id

member_id	pay_count	no_pay_count
11890	9	2
34860	2	0
49549	6	1
70955	2	1
85945	3	2
108191	0	1
145132	1	1
161249	2	0
173537	3	1
192368	4	1
201712	1	0
205394	1	1
216440	1	0
218095	1	1
219228	0	1
258721	1	0
285071	1	0
302700	1	0
364892	3	0
409520	1	0

 

3) COUNT DISTINCT 중복제거

- counter ~ distinct를 활용하여 중복값을 제거한 member_id의 수를 구할 수 있음.

SELECT count(distinct member_id) as member_count
FROM orders

 

4) 날짜 COUNT

- 연도별로 order가 몇건이었는지 파악해보기.

SELECT 
  count(case when order_time between '2015-01-01' and '2015-12-31' then 1 end) as order_2015,
  count(case when order_time between '2016-01-01' and '2016-12-31' then 1 end) as order_2016,
  count(case when order_time between '2017-01-01' and '2017-12-31' then 1 end) as order_2017,
  count(case when order_time between '2018-01-01' and '2018-12-31' then 1 end) as order_2018,
  count(case when order_time between '2019-01-01' and '2019-12-31' then 1 end) as order_2019,
  count(case when order_time between '2020-01-01' and '2020-12-31' then 1 end) as order_2020
FROM orders

order_2015	order_2016	order_2017	order_2018	order_2019	order_2020
9	10	14	9	10	4