I have four tables, all to do with tanks and weapons that are placed on the tanks and the total number of hits each weapon makes per test.
There are multiple tests, I want to find out which weapon has made the most hits overall (in my case there are two) with a date limitation applied. I have a query that gets the sum of each weapon's hits and outputs that in a descending list grouped by wep_id, like so:
The query I used to get that is:
SELECT SUM(hit_target) as total_hits,
FROM wep_performance_record w
GROUP BY wep_id
ORDER BY total_hits desc;
In my output I only want the two weps that had maximum hits, ie the top two on joint 26 score. I tried SELECT MAX(SUM(hit_target)) but then I get an invalid group function error. I tried to move the date selection into a HAVING statement after the GROUP but then I got an error 1064 (42000) near the having statement.
What am I doing wrong?
If it affects the solution, I intend to get info about those weps from other tables (wep_id is a primary key) and display that also, (such as which tank the wep was mounted on, the wep_type and which mounting point). I'm working on that query now, I assume I will need a JOIN to get those linked?
Many thanks in advance!