如何在查询中计算两个不同的东西

Question

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?

Answer 1

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