[mysql] count 조건 설정하는 방법 [통계쿼리정리]

 

안녕하세요 오늘은 count 조건 설정하는 방법에 대해 이야기해보겠습니다. 

흔히들 통계에서 사용할 때 많이 사용되는 쿼리입니다.

제가 하고자하는 것은 Group by와 count를 통해 원하는 계산식을 얻을 예정인데,

여기서 count에 조건을 넣는 공식이 포인트입니다.

그리고 이외에 통계에 사용되는 다른 쿼리들도 지속적으로 업데이트 할 예정입니다!

 

 

 

 

단순한 Group By

 

 

다들 아시다시피 단순하게 group by를 하면 아래와 같은 식이 나옵니다.

한 컬럼의 그룹에 대한 개수가 나타나게 되는거죠.

 

 

SELECT 
    cs_pm
    ,count(*)
FROM 
    tbl_counsel group by cs_pm

 

 

하지만 우리는 단순히 group by를 하는것에서 조건을 추가하여 여러 count 식을 구할 예정입니다.

아래에 정리해보았습니다.

 

 

 

 

---

 

 

 

 

 

 

1. count 안에서 조건설정

 

 

 

Group by 조건에 대한 count를 여러개 구할수도 있습니다.

---

공식은 아래에 있습니다.

count(case when '컬럼명'=1 then 1 end) as '별칭' 

---

조건을 이용하면 아래와 같이 여러개의 조건의 숫자를 구할 수 있는 것을 볼 수 있습니다.

 

 

SELECT 
    cs_pm as member
    ,count(case when cs_type=1 and cs_state=2 then 1 end) as onLineIngCnt
    ,count(case when cs_type=1 then 1 end) as onLineTotalCnt
    ,count(case when cs_type=2 then 1 end) as offLineIngCnt
    ,count(case when cs_type=2 then 1 end) as offLineTotalCnt
FROM 
    tbl_counsel
group by 
    member

 

 

 

 

 

 

 

 

 

2. count 중복제거  (distinct)

 

 

 

 

한 컬럼에서 중복을  제거한 값을 distinct를 통하여 숫자로 구할 수 있습니다.

 

 

select count(distinct cs_pm) as test from tbl_counsel

 

 

between을 통하여 기간안에의 데이터를 수로 구할 수 있습니다.

 

SELECT 
  count(case when 컬럼명 between '2020-10-20' and '2020-10-26' then 1 end) as 별칭
FROM 
    테이블명

 

 

 

 

 

반응형

 

 

 

 

 

 

8.  GROUP BY 없는 값 0으로 설정하는 방법

 

 

 

위에 사진과 같이 GROUP BY를 했을 경우 값이 없을 값도 0이 나올 수 있는 결과 값 구하는 방법입니다.

 

 

저는 한 컬럼 값에 1부터 5까지 값이 쌓이는 것에 대한 group by 통계값을 구하고자 합니다.

그런데 만약 1의 값이 없으면 위에  사진 처럼 결과값이 나타나지 않게 되고 통계값을 구하기 어려워집니다.

 

 

 

그래서 우선  별도의 테이블(tbl_common_code)에 1부터 5까지 값을 넣어 주고

 

SELECT
    a.code_name
    ,count(b.qe_idx) as qe_count
    ,a.main_code
FROM 
    tbl_common_code as a
        left outer join(
            select 
                *
            from
                tbl_query
            ) as b 
        on
            (b.qe_answer = a.main_code)
    where 
        a.ptrn_code = '922'
    group by 
        a.main_code
 

 

설문 값이 쌓이는 테이블(tbl_query)과 left outer join을 하면 됩니다.

여기서 중요한 부분은 기본값으로 1부터 5가 설정되어 있는

main_code부분을 group by해야 1부터 5까지 카운팅이 되는 점입니다.

 

 

 

 

 

 

 

 

 

 

 

9. GROUP BY Count가 빈칸일 경우 0으로 표시하는 방법

 

 

아래의 사진과 같이 GROUP BY count를 했을 때, 데이터가 없을 경우 빈칸으로 나타나게 됩니다.

이때 문제점은 빈칸일 경우 널포인트 에러가 날수도 있다는 점입니다.

이를 피하기 위해선 빈칸이 아닌 0으로 표시해서 에러를 피할 수 있습니다.

 

이럴 경우

1. from절에 서브쿼리를 선언하고 서브쿼리 안에서 group by를 통해 count를 구하고

2. count와 ifnull함수에 카운터값을 적용시키면

결과값이 빈칸이 아닌 0으로 표시되게 됩니다.

 

select 
    count(ifnull(a.qe_number,0)) as a
from
    (select 
        count(*) as qe_number
    from 
        tbl_query 
    where 
        del_yn='N' 
    group by 
        cret_ip
    ) as a
 

 

 

 

 

 

 

 

 

 

 

10. 기간만료 한달전 데이터 출력

 

SELECT 
    count(*) 
FROM 
    tbl_member
where 
 (me_edate BETWEEN CONCAT(YEAR(now()),(right(CURDATE(),6))) AND CONCAT(YEAR(now()),(right(DATE_ADD(CURDATE(), INTERVAL 1 MONTH),6))))

 

 

문자열 합치는 CONCAT 함수를 이용하여 년월일을 만들어주고

끝나는 일자를 기준으로 BETWEEN으로 비교해주면 됩니다!

 

 

 

 

 

 

 

 

11. 전체 / 오늘 / 어제 날짜 

 

 

SELECT 
COUNT(*) AS totalCount
,COUNT(case when DATE_FORMAT(cret_date, "%Y-%m-%d") = CURDATE() then 1 END) AS todayCount
,COUNT(case when DATE_FORMAT(cret_date, "%Y-%m-%d") = CURDATE()  - INTERVAL 1 DAY then 1 END) AS yesterayCount
FROM tbl_date

 

 

 

기존의 cret_date의 날짜형식 데이터를 DATE_FORMAT으로 스트링 형태로 변환하고,

현재 시간을 나타내는 CURDATE()를 매칭해주거나 INTERVAL을 통해 연산해주는 형태로 사용하시면 됩니다.