MySQL function to get totals from another table

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.

Leave a comment