MySQL Query Guidance Needed

Nicholas Leippe nick at leippe.com
Mon Sep 25 15:48:39 MDT 2006


On Monday 25 September 2006 14:58, Matthew Walker wrote:
> On Mon, September 25, 2006 2:47 pm, Hill, Greg wrote:
> > Maybe I'm misunderstanding something in your query, but it seems you
> > could just drop the GROUP BY to get a total sum, or GROUP BY player_id
> > to get sum per player (and drop the where clause).

If you do this then you are multiplying the value of each ship by the number 
of items in the ship.  The result you desire requires the sum of data with 
two different group criteria, thus it must be split up.

Pushing the entire query as-is into a subquery does the same thing as dropping 
the GROUP BY.

You need to separate it into the sum of two results:

SELECT
(SELECT # item total
 IFNULL(SUM(items.basePrice * lost.quantity * IF(items.techLevel > 1,
            10, 1)), 0) AS items_total
 FROM       mails 
 LEFT  JOIN lost  ON (lost.mail_id = mails.id) 
 LEFT  JOIN items ON (lost.item_id = items.id) 
 WHERE mails.player_id = 6
) +
(SELECT # item total
 SUM(ships.basePrice * IF(ships.techLevel > 1,
     10, 1)) AS ships_total
 FROM       mails 
 LEFT  JOIN lost           ON (lost.mail_id = mails.id) 
 LEFT  JOIN items AS ships ON (lost.item_id = ships.id)
 WHERE mails.player_id = 6
)

This can be done in more than one way, here's a method with a subselect, 
derived from your original query:

SELECT SUM(items_value) + SUM(ship_value)
FROM (
 SELECT 
 IFNULL(SUM(items.basePrice * lost.quantity * IF(items.techLevel > 1,
        10, 1)), 0) AS items_value,
 ships.basePrice * IF(ships.techLevel > 1,
        10, 1)      AS ship_value

 FROM       mails 
 LEFT  JOIN lost           ON (lost.mail_id = mails.id) 
 LEFT  JOIN items          ON (lost.item_id = items.id) 
 INNER JOIN items AS ships ON (mails.ship_id = ships.id) 
 WHERE mails.player_id = 6 
 GROUP BY mails.id
 ) a

You may want to EXPLAIN a few variations and play with indexes to find the one 
with the best performance.




More information about the PLUG mailing list