[DB] postgresql Count 함수 사용하기

SELECT
    COUNT(CASE WHEN score BETWEEN 9 AND 10 THEN 1 END) AS excellent_score,
    COUNT(CASE WHEN score BETWEEN 7 AND 8 THEN 1 END) AS good_score,
    COUNT(CASE WHEN score BETWEEN 1 AND 6 THEN 1 END) AS bad_score,
    COUNT((CASE WHEN score BETWEEN 1 AND 6 THEN 1 END) AND (CASE WHEN A = false AND B = false AND C = false THEN 1 END)) AS bad_score_unconfirmed,
    COUNT(CASE WHEN score IS NULL OR score = 0 THEN 1 END) AS not_rated_score
FROM
    t_table;

 

4번 째 COUNT절에서 에러가 났다. 

 

⚠️ 에러 내용

논리 연산자의 잘못된 사용이라는 에러이다. 

즉 앞의 CASE 문을 통해 결과값 1이 반환되는데 여기서 AND 연산자는 불리언 값만 사용할 수 있기에 에러가 발생한다. 

 

SELECT
    COUNT(CASE WHEN score BETWEEN 9 AND 10 THEN 1 END) AS excellent_score,
    COUNT(CASE WHEN score BETWEEN 7 AND 8 THEN 1 END) AS good_score,
    COUNT(CASE WHEN score BETWEEN 1 AND 6 THEN 1 END) AS bad_score,
    COUNT(CASE WHEN score BETWEEN 1 AND 6 AND A = false AND B = false AND C = false THEN 1 END) AS bad_score_unconfirmed,
    COUNT(CASE WHEN score IS NULL OR core = 0 THEN 1 END) AS not_rated_score
FROM
    t_table;

 

이렇게 단순하게 하면 되는 거였다.. ^^