MySQL查询统计用户手机号码后8位相同的乘客数量

SELECT user.username, COUNT(CASE WHEN SUBSTR(passenger.phone, 10) = '8' THEN 1 ELSE NULL END) AS count FROM user LEFT JOIN passenger ON passenger.username = user.username GROUP BY user.username