MySQL Query Guidance Needed

[Hill, Greg]

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'm assuming mails =
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.

Greg

> -----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.
> */