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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of > Matthew Walker > Sent: Monday, September 25, 2006 2:05 PM > To: [email protected] > 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. */
