MySQL Query Guidance Needed

Nicholas Leippe nick at leippe.com
Mon Sep 25 16:12:28 MDT 2006


On Monday 25 September 2006 15:57, Matthew Walker wrote:
> > 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.
>
> Really? Because my tests don't bear that out. Odd. I thought of that too,
> and made sure to sanity-check the totals I was getting.

Well, your query as it is is correct--it gets the value per ship. If you have:

item value, ship value
----------------------
1, 4
2, 4
3, 5
4, 5

your original query produces:
7
12

pushing that into a subquery and summing it should work as expected--in fact, 
I now realize that is the same as the one I did, but even simpler:

SELECT SUM(value)
FROM (
 SELECT 
 IFNULL(SUM(items.basePrice * lost.quantity * IF(items.techLevel > 1,
        10, 1)), 0) + 
 ships.basePrice * IF(ships.techLevel > 1,
        10, 1)      AS 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 don't want 4 and 5 added to the sum twice, which is what you get when you 
drop the GROUP BY.  (you get (1 + 4) + (2 + 4) ...). What you really want is 
to get:

sum(value of items in ship), ship value
---------------------------------------
3, 4
7, 5

and then sum all of that together (summing the result of your query)

or sum(value of items in all ships) + sum(value of all ships) (the query I 
showed with two subselects).




More information about the PLUG mailing list