I got this query:
$q = $mysql->query("
SELECT *, COUNT(`crimeDescription`) AS total_count
FROM `crimes`
GROUP BY `crimeDescription`
ORDER BY COUNT(`crimeDescription`) DESC
Limit 15") or die($mysql->error);
I also want to get the person who has had this crime issued most times against him (crimeIssuedTo)
Example:
1 | Grand Theft Auto | Most to: User_A | Total: 500
2 | Attempted Murder | Most to: User_C | Total: 453
3 | Assault | Most to: User_D | Total: 451
How can I do that?
How about something like the following:
SELECT *, COUNT(*) AS total_count,
(
SELECT p.Username
FROM crimes AS sc
INNER JOIN players AS p ON p.ID = sc.crimeIssuedTo
WHERE sc.crimeDescription = sm.crimeDescription
GROUP BY sc.crimeIssuedTo
ORDER BY COUNT(*) DESC
LIMIT 1
) AS PersonWithMostOffenses
FROM crimes AS sm
GROUP BY sm.crimeDescription
ORDER BY COUNT(*) DESC
LIMIT 15