On Mon, September 25, 2006 3:48 pm, Nicholas Leippe wrote: > 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.
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. > > 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. Excellent reccomendations, and I'll definately have to take a look at the performance. This is the trickiest querying I've done in a while, and it's fun. I love a challenge. Note that despite 'solving' the problem with a stored function, I'm still not satisfied. :) -- Matthew Walker Kydance Hosting & Consulting LAMP Specialist /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
