1

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?

CMPS
  • 7,733
  • 4
  • 28
  • 53
Chriham
  • 49
  • 5
  • Please put your tables structure – CMPS Apr 07 '14 at 17:18
  • possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Marc B Apr 07 '14 at 17:20

1 Answers1

0

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
Linger
  • 14,942
  • 23
  • 52
  • 79
  • Hey thanks, it works, except it returns the ID and not the username on PersonsWithMostOffenses. What if I want to select Username from players where ID is PersonWithMostOffenses in the same query? – Chriham Apr 07 '14 at 17:56
  • **@user3494083**, I updated my answer to join the `players` table in the sub query. Let me know if that works for you. – Linger Apr 07 '14 at 18:00
  • Thanks a bunch! Also, how can I add another ( SELECT ) as ... in the same query, as I'd like to get PersonIssuedMost aswell – Chriham Apr 07 '14 at 18:04
  • You would just follow the same pattern as in the above sub query. – Linger Apr 07 '14 at 18:06
  • Give the `PersonIssuedMost` query a try and if you have any problems with it, post another question. – Linger Apr 07 '14 at 18:08