MySQL Query Guidance Needed

Matthew Walker rorith at kydance.net
Mon Sep 25 14:58:07 MDT 2006


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).

I've tried that as well, and it doesn't provide accurate numbers. I can't
figure out exactly what's going on, but it's definately not counting some
of the columns that it should.

Does anyone have tips on troubleshooting this sort of problem? Logically,
by changing the GROUP BY to the player_id, my SUM()'s should still pick up
all the same rows. But it's definately not working that way, and I'm not
clear on what it /is/ doing.

As an example, here's the output of two queries. The first is the one I
included in my first mail, and the second is simply the GROUP BY changed
to mails.player_id.

+------------+
| totalValue |
+------------+
|   72245962 |
|  120939724 |
|    1000000 |
|   72374612 |
|    6510364 |
|    1000000 |
|  115857656 |
|   10754932 |
|    1000000 |
|    3564440 |
|    5000000 |
+------------+
11 rows in set (0.00 sec)

+------------+
| totalValue |
+------------+
|   99633898 |
+------------+
1 row in set (0.01 sec)

Those numbers just don't add up, obviously.

> I'm assuming mails [are]
> kills, but you weren't so clear on that.  And, I didn't spend too long
> looking at it, so I might be discounting something.  Also, since you
> don't care about non-existant entries in the 'lost' table (i.e., if
> nothing was lost, it's zero, so ignore it), you could do a JOIN instead
> of LEFT JOIN and you'd never have nulls to deal with.

Been too long since I've used a natural JOIN, (I tend to always use INNER
or LEFT, to be explicit), but wouldn't that not return a row at all if no
items were destroyed? The ship destruction is part of the 'mails' record.
And just to clear that up, yes, 'mails' is kills, one record per kill. I
suppose I could normalize the ship ID out to the loss table, and add a new
location of 'ship'. This also seems a bit de-normalized though, since the
'ship' is an intrinsic property of the kill, and can only have one
possible value per mail.

>> -----Original Message-----
>> From: plug-bounces at plug.org [mailto:plug-bounces at plug.org] On Behalf
> Of
>> Matthew Walker
>> Sent: Monday, September 25, 2006 2:05 PM
>> To: plug at plug.org
>> Subject: MySQL Query Guidance Needed
>>
>> I'm trying to do a rather tricky query in MySQL, and I'm a bit
> stumped. I
>> know I can do what I want if I just offload the work to PHP, but for
> the
>> sake of the learning, I'm trying to do it inside SQL.
>>
>> A bit of background: This is a 'point tracking' site for PVP in EVE
>> Online. It parses kill reports, and stores them in the database. Now
> I'm
>> trying to get it to tally up points per kill, based on the value of
> the
>> items destroyed.
>>
>> Relevant Schema:
>>
>> CREATE TABLE `items` (
>>   `id` int(11) NOT NULL auto_increment,
>>   `typeName` char(100) default NULL,
>>   `graphicID` int(11) default NULL,
>>   `basePrice` double default NULL,
>>   `techLevel` tinyint(3) unsigned NOT NULL,
>>   PRIMARY KEY  (`id`),
>>   UNIQUE KEY `typeName` (`typeName`),
>>   KEY `graphicID` (`graphicID`)
>> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
>>
>> -- --------------------------------------------------------
>>
>> CREATE TABLE `lost` (
>>   `mail_id` bigint(20) unsigned NOT NULL,
>>   `item_id` bigint(20) unsigned NOT NULL,
>>   `quantity` bigint(20) unsigned NOT NULL,
>>   `location` enum('cargo','fit','drone_bay') NOT NULL
>> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>>
>> -- --------------------------------------------------------
>>
>> CREATE TABLE `mails` (
>>   `id` bigint(20) unsigned NOT NULL auto_increment,
>>   `player_id` bigint(20) unsigned NOT NULL,
>>   `corporation_id` bigint(20) unsigned NOT NULL,
>>   `alliance_id` bigint(20) unsigned NOT NULL,
>>   `ship_id` bigint(20) unsigned NOT NULL,
>>   `system_id` bigint(20) unsigned NOT NULL,
>>   `timestamp` datetime NOT NULL,
>>   `hash` varchar(32) NOT NULL,
>>   `mail` longtext NOT NULL,
>>   PRIMARY KEY  (`id`)
>> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
>>
>> I've got a query that does most of the work of generating the cost of
> all
>> of a person's losses:
>>
>> SELECT IFNULL(SUM(items.basePrice * lost.quantity * IF(items.techLevel
>>
>> 1, 10, 1)), 0)+(ships.basePrice * IF(ships.techLevel > 1, 10, 1)) AS
>> totalValue 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
>>
>> However, this returns 1 row per kill, and I would like to get it to
> return
>> simply one value, that is a total of all the losses. Simple to do in
> PHP
>> with what I have, but I can't find a way to do it directly in the SQL.
>>
>> I've tried SUM()'ing a subselect, but that returns null, so I guess I
>> don't understand subselects. :)
>>
>> Oh, and flame my language/DB choice all you want. I know them, and
> they're
>> good enough for me. If there isn't a way to do this in-db, I'll
> survive
>> with doing it in the PHP.
>>
>> Thanks.
>> --
>> 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.
>> */
>
> /*
> 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