SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting multiple max values from a sum query

    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.



    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TankSim View Post
    Why do I need to get the max test_date?
    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/...n-columns.html)


    Quote Originally Posted by TankSim View Post
    how big will it be when I try to add the info from the other tables?
    slightly bigger
    Code:
    SELECT m.wep_id
         , m.total_hits
         , m.latest_date
         , x.foo
         , y.bar
      FROM ( 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 )              
           ) 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, that's genius!
    Thanks for the pointers, I am in the right direction now.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •