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