[ORACLE]이메일 도메인 걸러내기

SELECT SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL))) AS DOMAIN,
COUNT(SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))) AS DOMAIN_CNTS
FROM AUTO120
WHERE WORKDAY='20121116'
AND SEQNO=5
GROUP BY SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))
HAVING COUNT(SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))) > 0
ORDER BY COUNT(SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))) DESC

 

SELECT
DOMAIN,
COUNT(DOMAIN) AS DOMAIN_CNTS
FROM EBAD1060
WHERE POST_ID = '2008101400001'
GROUP BY DOMAIN
HAVING COUNT(DOMAIN) > 100
ORDER BY COUNT(DOMAIN) DESC