[Mysql || MariaDB] Select 문 한번에 여러 개 Count()카운트 조회

사용 이유

---

Select 문으로 다양한 Count 개수를 뽑아 낼 때

 

 

 

문법 및 예시

---

select count(CASE WHEN column=조건 THEN 1 END) as 별명(생략가능),
count(CASE WHEN column=조건 THEN 1 END) as 별명(생략가능) from 테이블명;

 

먼저 예시로 사용할 테이블 및 데이터 하나 만들겠습니다.

create table expenditure(

id varchar(64),
timestamp timestamp,
money int,
primary key(id,timestamp)

);

insert into expenditure values('gildong','2019-10-19',100000);
insert into expenditure values('gildong','2019-10-20',70000);
insert into expenditure values('gildong','2019-10-21',3000);
insert into expenditure values('gildong','2019-10-22',7700);
insert into expenditure values('gildong','2019-10-23',50100);
insert into expenditure values('samsam','2019-10-19',50000);
insert into expenditure values('samsam','2019-10-20',60000);
insert into expenditure values('samsam','2019-10-21',90000);
insert into expenditure values('samsam','2019-10-22',1110000);
insert into expenditure values('samsam','2019-10-23',31000);
insert into expenditure values('YUMDDA','2019-10-19',11200000);
insert into expenditure values('YUMDDA','2019-10-20',122300000);
insert into expenditure values('YUMDDA','2019-10-21',10330000);
insert into expenditure values('YUMDDA','2019-10-22',3541100);
insert into expenditure values('YUMDDA','2019-10-23',10000000);

 

 

#기본 MySQL 카운트 함수

select count(*) from expenditure;

 

상황1. id가 gildong인 사람만 카운트 , id가 samsam인 사람만 카운트

SELECT COUNT(CASE WHEN id='gildong' THEN 1 END) AS gildongCnt,
COUNT(CASE WHEN id='samsam' THEN 1 END) AS samsamCnt
FROM expenditure;

 

#조건 여러개(다중) 카운트

상황2. id가 YUMDDA인 사람이 돈을 1천만이상 쓴 데이터만 카운트 , id가 samsam인 사람만 카운트

SELECT COUNT(CASE WHEN id='YUMDDA' THEN 1 END AND CASE WHEN money > 10000000 THEN 1 END) AS YUMDDACnt, 
COUNT(CASE WHEN id='samsam' THEN 1 END) AS samsamCnt
FROM expenditure;

상황3. 다른 테이블의 특정 컬럼 Count 개수 구하기

SELECT COUNT(CASE WHEN id='gildong' THEN 1 END) AS gildongCnt,
COUNT(CASE WHEN id='samsam' THEN 1 END) AS samsamCnt,
(SELECT COUNT(*)
FROM RelationTable) AS relationCnt
FROM expenditure;

 

예제 파일 제공

---

[MariaDB] Select 문 여러개 Count 구하기.sql

0.00MB