반응형
상위 9개로 정렬된 카운트(count_id)를 기준으로 상위 9개 및 기타를 가져온 다음 다른 카운트를 'club' 범주로 묶는 방법
다음 쿼리는 모든 것을 실행합니다.
SELECT count(entity_id), cim.institute_id, cfi.name_ FROM ci_certification_students cis INNER JOIN ci_certification_master cim ON cis.certificate_id = cim.certificate_id
INNER JOIN ci_finalized_institute cfi ON cfi.institute_id=cim.institute_id
WHERE cis.status_id=4 AND cim.institute_id is not null group by cim.institute_id order by count(entity_id) desc limit 100;
다음을 시도했습니다.
SELECT seqnum,institute_id, (CASE WHEN seqnum > 9 THEN 'others' ELSE name_ END) as name_, sum(total_certi) FROM
(select count(cis.entity_id) as total_certi, cim.institute_id, cfi.name_ as certiName, ROW_NUMBER() OVER (ORDER BY COUNT(cis.entity_id) desc) as seqnum
FROM certificationdb.ci_certification_students cis
INNER JOIN ci_certification_master cim ON cis.certificate_id = cim.certificate_id
INNER JOIN certificationdb.ci_finalized_institute cfi ON cim.institute_id=cfi.institute_id
WHERE cis.status_id=4 AND cim.institute_id is not null group by cim.institute_id ORDER BY
count(cis.entity_id)desc)c GROUP BY (CASE WHEN seqnum> 9 THEN 'others' ELSE name_ END)
ORDER BY seqnum;
어떤 도움이라도 주시면 감사하겠습니다...
사용하다row_number()
그리고 두 가지 수준의 집계:
SELECT (CASE WHEN seqnum < 10 THEN institute_id END) as institute_id,
(CASE WHEN seqnum < 10 THEN name_ ELSE 'OTHERS' END) as name,
SUM(cnt) as cnt
FROM (SELECT cim.institute_id, cfi.name_, COUNT(*) as cnt,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum
FROM ci_certification_students cis INNER JOIN
ci_certification_master cim
ON cis.certificate_id = cim.certificate_id INNER JOIN
ci_finalized_institute cfi ON
cfi.institute_id = cim.institute_id
WHERE cis.status_id = 4 AND cim.institute_id is not null
GROUP BY cim.institute_id, cfi.name_
) c
GROUP BY institute_id, name
ORDER BY MIN(seqnum);
언급URL : https://stackoverflow.com/questions/60043750/how-to-fetch-top-9-and-others-based-on-countentity-id-sorted-highest-top-9-and
반응형
'programing' 카테고리의 다른 글
RecyclerView 어댑터 데이터 업데이트 방법 (0) | 2023.09.04 |
---|---|
다른 열과 같은 열에 SQL 가입 (0) | 2023.09.04 |
원형으로 글꼴 멋진 아이콘 만들기? (0) | 2023.09.04 |
OpenCV & Python - 이미지가 너무 커서 표시할 수 없습니다. (0) | 2023.09.04 |
시퀀스 점(c) (0) | 2023.09.04 |