Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Unanswered: Need a hand with MAX/SUM -- thanks

    Hi,

    I realize this is an easy problem, but the solution escapes me during this Friday's burn-out.

    Straight to the problem:

    Table:
    Code:
    CREATE TABLE `reports` (
      `reportid` int(13) NOT NULL auto_increment,
      `repid` varchar(150) NOT NULL,
      `teamid` int(13) NOT NULL default '0',
      `attach` int(13) NOT NULL default '0',
      `bonus` int(13) NOT NULL default '0',
      `datebegin` date default NULL,
      `dateend` date default NULL,
      `improvement` int(4) unsigned default NULL,
      PRIMARY KEY  (`reportid`),
      KEY `teamid` (`teamid`)
    ) ENGINE=MyISAM AUTO_INCREMENT=616 DEFAULT CHARSET=latin1
    I need to find for each team, the user whose sum of attach + bonus is the greatest, for the latest datebegin value.

    So as you can imagine, this query returns, ordered by team, the descending-order list for points leaders:

    Code:
    SELECT repid, teamid, attach + bonus AS total
    FROM reports
    WHERE datebegin = ( SELECT MAX(datebegin) FROM reports )
    ORDER BY teamid, total DESC
    I need to adjust this query to return only the points leader per teamid. (so 4 teams, would only return 4 rows).

    Thanks for your help!

  2. #2
    Join Date
    Nov 2004
    Posts
    35
    I've found this solution, but is it correct/optimal?

    Code:
    SELECT * FROM (
    SELECT reportid, repid, teamid, attach + bonus AS total
    FROM reports
    WHERE datebegin = ( SELECT MAX(datebegin) FROM reports )
    ORDER BY teamid, total DESC
    ) AS X
    GROUP BY teamid

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no -- mixing the dreaded, evil "select star" with GROUP BY is flat out wrong

    GROUP BY produces one row for each value in the group by column(s)

    so your outer query produces one row per teamid

    the values for all the other non-aggregate columns in the SELECT are indeterminate

    also, ORDER BY in a subquery has no effect

    could you please try to explain "the user whose sum of attach + bonus is the greatest, for the latest datebegin value"

    which do you want -- the greatest total or the latest date?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    35
    I want both!

    The greatest set of totals for the latest date!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i get it, i just misunderstood your question

    so this part is correct --
    Code:
    SELECT repid, teamid, attach + bonus AS total
    FROM reports
    WHERE datebegin = ( SELECT MAX(datebegin) FROM reports )
    now the only tough part is finding the greatest total for each team out of that

    Code:
    SELECT *
      FROM ( 
           SELECT repid
                , teamid
                , attach + bonus AS total
             FROM reports
            WHERE datebegin = 
                 ( SELECT MAX(datebegin) 
                     FROM reports )
           ) AS t
     WHERE 0 =
           ( SELECT COUNT(*)
              FROM ( 
                   SELECT repid
                        , teamid
                        , attach + bonus AS total
                     FROM reports
                    WHERE datebegin = 
                         ( SELECT MAX(datebegin) 
                             FROM reports )
                   ) AS t2
             WHERE total > t.total 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Posts
    35
    Thanks for the continued discussion.

    That last query though, only returns the greatest total out of all teams. What I need, is the greatest total, per team.

    The query I posited in message 2, albeit perhaps evil, seems to work.. The ORDER on the subquery is actually what is returning the proper rows... If I remove that, the results are no longer accurate...

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Atari
    That last query though, only returns the greatest total out of all teams.
    aargh, i forgot a line
    Code:
    SELECT *
      FROM ( 
           SELECT repid
                , teamid
                , attach + bonus AS total
             FROM reports
            WHERE datebegin = 
                 ( SELECT MAX(datebegin) 
                     FROM reports )
           ) AS t
     WHERE 0 =
           ( SELECT COUNT(*)
              FROM ( 
                   SELECT repid
                        , teamid
                        , attach + bonus AS total
                     FROM reports
                    WHERE datebegin = 
                         ( SELECT MAX(datebegin) 
                             FROM reports )
                   ) AS t2
             WHERE total > t.total 
                AND teamid = t.teamid
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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