|You track squash court bookings and fees. A court booking has one fee, but it has two member references, and those can be the same if one member has brought a guest. For each booking row, the fee is to be split between the two members. What query correctly aggregates fees including cases where the two members of record are the same?
DROP TABLE IF EXISTS bookings; CREATE TABLE bookings ( court_id int(11) NOT NULL, member1 int(11) NOT NULL, member2 int(11) NOT NULL, time timestamp NOT NULL, fee decimal(5,2) NOT NULL ); INSERT INTO bookings ( court_id , member1 , member2 , time , fee ) VALUES (1, 1000, 1001, '2009-09-09 15:49:38', 3.00), (2, 1000, 1000, '2009-09-08 15:50:04', 3.00);For this data the correct result is
member fees 1000 4.50 1001 1.50An efficient solution, posted on a MySQL forum ...
SELECT member, ROUND(SUM(fee/2),2) AS total FROM ( SELECT member1 AS member, fee FROM bookings UNION ALL SELECT member2, fee FROM bookings ) AS tmp GROUP BY member;
Last updated 9 Sep 2009