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.