Need a hand with MAX/SUM -- thanks

Atari · #1 (**permalink**) 11-07-08, 11:26

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!

Atari · #2 (**permalink**) 11-07-08, 11:30

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

r937 · #3 (**permalink**) 11-07-08, 11:48

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?

Atari · #4 (**permalink**) 11-07-08, 12:16

I want both!

The greatest set of totals for the latest date!

r937 · #5 (**permalink**) 11-07-08, 12:54

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 
       )

Atari · #6 (**permalink**) 11-07-08, 15:21

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...

r937 · #7 (**permalink**) 11-07-08, 15:23

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
       )

Internet Services

Web Development

Digital Marketing

Consumer Tech