The following query executes everything:
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;
I tried the following:
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;
Any help would be appreciated…
Advertisement
Answer
Use row_number()
and two levels of aggregation:
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);