반응형

사용 이유


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

반응형

+ Recent posts