Using the advice I got in this thread, I came up with these two stored
functions that do exactly what I want:
CREATE FUNCTION `kill_points`(check_id BIGINT(20) UNSIGNED) RETURNS double
READS SQL DATA
BEGIN
DECLARE total DOUBLE DEFAULT 0.0;
SELECT SUM( value ) INTO total FROM (SELECT (IFNULL( SUM(
items.basePrice * lost.quantity * IF( items.techLevel >1, 10, 1 ) ) , 0
) + ships.basePrice * IF( ships.techLevel >1, 10, 1 )) / count(DISTINCT
inv.player_id) AS value FROM kb__killmails AS mails INNER JOIN
kb__involved AS inv ON (mails.id = inv.mail_id) LEFT JOIN kb__items_lost
AS lost ON ( lost.mail_id = mails.id ) LEFT JOIN kb__items AS items ON (
lost.item_id = items.typeID ) INNER JOIN kb__items AS ships ON (
mails.ship_id = ships.typeID ) WHERE mails.id IN (SELECT inv.mail_id AS
involved FROM kb__involved AS inv INNER JOIN kb__killmails AS kills ON
(inv.mail_id=kills.id) WHERE inv.player_id=check_id GROUP BY
inv.mail_id) GROUP BY mails.id) AS a;
RETURN total;
END
CREATE FUNCTION `loss_points`(check_id BIGINT(20) UNSIGNED) RETURNS double
READS SQL DATA
BEGIN
DECLARE total DOUBLE DEFAULT 0.0;
SELECT SUM( value ) INTO total 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
kb__killmails AS mails LEFT JOIN kb__items_lost AS lost ON (
lost.mail_id = mails.id ) LEFT JOIN kb__items AS items ON ( lost.item_id
= items.typeID ) INNER JOIN kb__items AS ships ON ( mails.ship_id =
ships.typeID ) WHERE mails.player_id=check_id GROUP BY mails.id) AS a;
RETURN total;
END
Thanks again for the help, everyone. You've helped me reach a better
understanding of one of the newer features of MySQL.
--
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.
*/
