Artful Common Queries

Aggregate across columns

from the Artful Common Queries page

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.50

An 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