Selecting multiple max values from a sum query

Databases
1

Hi folks

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.

450×536 73.7 KB

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,
wep_id
test_date
FROM wep_performance_record w
WHERE test_date>90101
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!

2 
SELECT wep_id
     , SUM(hit_target) as total_hits
     , MAX(test_date ) AS latest date
  FROM wep_performance_record
 WHERE test_date > 90101
GROUP
    BY wep_id
HAVING SUM(hit_target) =
       ( SELECT MAX(total_hits)
           FROM ( SELECT SUM(hit_target) as total_hits
                    FROM wep_performance_record
                   WHERE test_date > 90101
                  GROUP
                      BY wep_id ) AS s )
3

Whew!

That is complex, but it works, thanks!
Just a couple of things I don’t understand:

Why do I need to get the max test_date?
Why do I need the last bit, AS s )? EDIT: okay I see, it is a derived table so needs an alias, I could call it anything.

The worrying thing is I don’t reckon I could have solved this by myself. If the query for that bit alone is such a big chunk then how big will it be when I try to add the info from the other tables?

By the way r937, I think there should be a cookies-for-tips feature, I am not kidding, I value you guys’ assistance and I want to show my appreciation. Like Hannibal Lekter says, there needs to be quid pro quo :wink:

4

you don’t, but since you had test_date in your SELECT cause but not in your GROUP BY clause, the value being returned would be indeterminate, i.e. any one of the test_date values in the group (see http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html)

slightly bigger :slight_smile:

[COLOR="Blue"]SELECT m.wep_id
     , m.total_hits
     , m.latest_date
     , x.foo
     , y.bar
  FROM ([/COLOR] SELECT wep_id                                         
              , SUM(hit_target) as total_hits                  
              , MAX(test_date ) AS latest date                 
           FROM wep_performance_record                         
          WHERE test_date > 90101                              
         GROUP                                                 
             BY wep_id                                         
         HAVING SUM(hit_target) =                              
                ( SELECT MAX(total_hits)                       
                    FROM ( SELECT SUM(hit_target) as total_hits
                             FROM wep_performance_record       
                            WHERE test_date > 90101            
                           GROUP                               
                               BY wep_id ) AS s )              
       [COLOR="blue"]) AS m
INNER
  JOIN other_table AS x
    ON x.wep_id = m.wep_id
INNER
  JOIN another_table AS y
    ON y.wep_id = m.wep_id[/COLOR]
5

Rudy, that’s genius!
Thanks for the pointers, I am in the right direction now.