MySQL에서 하위 문자열 발생을 계산하고 빈도별로 결과를 정렬하는 방법은 무엇입니까?

SELECT (CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, substr, ''))) / CHAR_LENGTH(substr) AS cnt
...
ORDER BY cnt DESC

'127.0.0.1'
'honeypot'
'honeypot'
'localhost'
'localhost'

SELECT host, (CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, 'l', ''))) / CHAR_LENGTH('l') AS cnt
FROM user
ORDER BY cnt DESC

| host      | cnt    |
|-----------+--------|
| localhost | 2.0000 |
| localhost | 2.0000 |
| honeypot  | 0.0000 |
| honeypot  | 0.0000 |
| 127.0.0.1 | 0.0000 |