MySQL Query Guidance Needed

Matthew Walker rorith at kydance.net
Tue Sep 26 11:44:13 MDT 2006


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



More information about the PLUG mailing list