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).
/*
PLUG: http://plug.org, #utah on irc.freenode.net
Unsubscribe: http://plug.org/mailman/options/plug
Don't fear the penguin.
*/
