How to modify mysql query?

Question

MySQL

Hello, please help to finalize the request in the muscle,

SELECT *  
FROM `groups`, `players` 
LEFT JOIN `user_info_cache` ON `user_info_cache`.`steamid`=`players`.`SID` 
WHERE `players`.`group`=`groups`.`txtid` 
ORDER BY `groups`.`id` ASC, `user_info_cache`.`nickname` ASC

I need to make it so that the sorting is only from the violations table by the number of records of each SID, please help