Thank you for your insight. I didn't fully understand the power of subselects. :) Never thought of using one as a FROM source before. I was trying to do what you show, but going about it completely the wrong way.
On Mon, September 25, 2006 4:12 pm, Nicholas Leippe wrote: > 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. > */ > -- 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. */
