Sometimes when you want to run reports on users, maybe that have sales orders associated to them, you maybe just want to put a total into the row.
Of course you could do this by looping and run a query for each user, but thats pretty inefficient.
This is a MySQL stored function that would do something like that for you.
DELIMITER $$ DROP FUNCTION IF EXISTS `getColumnTotalByDriver` $$ CREATE DEFINER=`root`@`localhost` FUNCTION `getColumnTotalByDriver`(`driver_id` INT, `columnName` VARCHAR(64), `start_date` DATETIME, `end_date` DATETIME) RETURNS FLOAT(18,2) DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLARE total FLOAT(18,2); SELECT SUM(receipt_amount) from orders WHERE accepted_by = driver_id AND charge_amount > 0 AND date_delivered >= start_date AND date_delivered <= end_date INTO total; RETURN total; END $$ DELIMITER ;
To use that function you would do something like this.
SELECT * , getColumnTotalByDriver( u.user_id, '2012-11-08 00:00:00', '2012-12-08 23:59:59' ) AS total
FROM user u
WHERE user_level =10
Thanks to this post for getting me started.