MySQL Query Guidance Needed

[Matthew Walker]

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