Employee sales commission rates increase with sales totals according to specified bands of sales total amounts—like a graduated income tax in reverse. To compute total commissions due each employee, we need to aggregate twice: first to get sales per employee, then to get commissions per employee:
-- ---------------------------------------------------------------------------
-- Nested aggregation
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=714
-- ---------------------------------------------------------------------------
-- Employee sales commission rates increase with sales totals according to
-- specified bands of sales total amounts—like a graduated income tax in reverse.
-- To compute total commissions due each employee, we need to aggregate twice:
-- first to get sales per employee, then to get commissions per employee:
DROP TABLE IF EXISTS sales, commissions;
CREATE TABLE sales(employeeID int,sales int);
INSERT INTO sales VALUES(1,2),(1,5),(1,7),(2,9),(2,15),(2,12);
SELECT * FROM sales;
-- +------------+-------+
-- | employeeID | sales |
-- +------------+-------+
-- | 1 | 2 |
-- | 1 | 5 |
-- | 1 | 7 |
-- | 2 | 9 |
-- | 2 | 15 |
-- | 2 | 12 |
-- +------------+-------+
CREATE TABLE commissions(
comstart DECIMAL(6,2),
commend DECIMAL(6,2),
comfactor DECIMAL(6,2),
pct INT
);
INSERT INTO commissions VALUES
(1.00,10.00,0.10,10),(11.00,20.00,0.20,20),(21.00,30.00,0.30,30),(31.00,40.00,0.40,40);
SELECT * FROM commissions;
-- +----------+---------+-----------+------+
-- | comstart | commend | comfactor | pct |
-- +----------+---------+-----------+------+
-- | 1.00 | 10.00 | 0.10 | 10 |
-- | 11.00 | 20.00 | 0.20 | 20 |
-- | 21.00 | 30.00 | 0.30 | 30 |
-- | 31.00 | 40.00 | 0.40 | 40 |
-- +----------+---------+-----------+------+
-- The first problem is to work out how commission ranges map to sales totals to
-- determine base amounts for calculation of each part-commission. We assume
-- the ranges are inclusive, ie a range that starts at 1 euro is meant to
-- include that first euro:
if amt < comstart, base amount = 0
if amt <= commend, base amount = amt-comstart+1
if amt > commend, base amount = commend - comstart+1
-- This is a nested IF():
IF(s.amt<c.comstart,0,IF(s.amt<=c.commend,s.amt-c.comstart,c.commend-c.comstart))
-- The second problem is how to apply every commission range row to every
-- employee sales sum. That's a CROSS JOIN between aggregated sales and
-- commissions:
SELECT *
FROM (
SELECT employeeID,SUM(sales) AS amt
FROM sales
GROUP BY employeeID
) AS s
JOIN commissions
ORDER BY s.employeeID;
-- +------------+------+----------+---------+-----------+------+
-- | employeeID | amt | comstart | commend | comfactor | pct |
-- +------------+------+----------+---------+-----------+------+
-- | 1 | 14 | 1.00 | 10.00 | 0.10 | 10 |
-- | 1 | 14 | 11.00 | 20.00 | 0.20 | 20 |
-- | 1 | 14 | 21.00 | 30.00 | 0.30 | 30 |
-- | 1 | 14 | 31.00 | 40.00 | 0.40 | 40 |
-- | 2 | 36 | 31.00 | 40.00 | 0.40 | 40 |
-- | 2 | 36 | 1.00 | 10.00 | 0.10 | 10 |
-- | 2 | 36 | 11.00 | 20.00 | 0.20 | 20 |
-- | 2 | 36 | 21.00 | 30.00 | 0.30 | 30 |
-- +------------+------+----------+---------+-----------+------+
-- Now check how the formula applies on every commission band for every sales total:
SELECT
s.employeeID,s.amt,c.comstart,c.commend,
IF(s.amt<=c.comstart,0,
IF( s.amt < c.commend, s.amt-c.comstart+1, c.commend-c.comstart+1 )
) AS base,
c.comFactor AS ComPct,
IF(s.amt<=c.comstart,0,
IF(s.amt<c.commend,s.amt-c.comstart+1,c.commend-c.comstart+1)
) * comFactor AS Comm
FROM (
SELECT employeeID,SUM(sales) AS amt
FROM sales
GROUP BY employeeID
) AS s
JOIN commissions c
ORDER BY s.employeeID,comstart;
-- +------------+------+----------+---------+-------+--------+--------+
-- | employeeID | amt | comstart | commend | base | ComPct | Comm |
-- +------------+------+----------+---------+-------+--------+--------+
-- | 1 | 14 | 1.00 | 10.00 | 10.00 | 0.10 | 1.0000 |
-- | 1 | 14 | 11.00 | 20.00 | 4.00 | 0.20 | 0.8000 |
-- | 1 | 14 | 21.00 | 30.00 | 0.00 | 0.30 | 0.0000 |
-- | 1 | 14 | 31.00 | 40.00 | 0.00 | 0.40 | 0.0000 |
-- | 2 | 36 | 1.00 | 10.00 | 10.00 | 0.10 | 1.0000 |
-- | 2 | 36 | 11.00 | 20.00 | 10.00 | 0.20 | 2.0000 |
-- | 2 | 36 | 21.00 | 30.00 | 10.00 | 0.30 | 3.0000 |
-- | 2 | 36 | 31.00 | 40.00 | 6.00 | 0.40 | 2.4000 |
-- +------------+------+----------+---------+-------+--------+--------+
-- Finally, SUM formula results to aggregate commissions on aggregated sales:
SELECT
s.employeeID,
ROUND(s.amt,2) AS Amount,
ROUND( SUM(IF(s.amt<=c.comstart,0,
IF(s.amt<=c.commend,s.amt-c.comstart+1,c.commend-c.comstart+1)
) * c.pct/100),2 ) AS Commissions
FROM (
SELECT employeeID,SUM(sales) AS amt
FROM sales
GROUP BY employeeID
) AS s
JOIN commissions c
GROUP BY s.employeeID;
-- +------------+--------+-------------+
-- | employeeID | Amount | Commissions |
-- +------------+--------+-------------+
-- | 1 | 14.00 | 2.00 |
-- | 2 | 36.00 | 8.00 |
-- +------------+--------+-------------+
-- Here is another example. We track passenger flight bookings in three tables:
-- flight, booking, passenger. To report all destinations per passenger,
-- retrieve DISTINCT passenger-destination combos, then count them:
DROP TABLES IF EXISTS flight, booking, passenger;
CREATE TABLE flight(flight CHAR(12),source CHAR(12),destination CHAR(12));
INSERT INTO flight VALUES
('ab123','dublin','london'),('bc123','prague','london'),('cd123','stuttgart','paris'),('de123','paris','madrid');
CREATE TABLE booking(flight CHAR(5),seat CHAR(2),passenger_id INT NOT NULL);
INSERT INTO booking VALUES
('ab123','a2',1),('bc123','a1',2),('bc123','a2',1),('cd123','a3',1);
CREATE TABLE passenger(passenger_id INT, name VARCHAR(12));
INSERT INTO passenger VALUES (1,'john'),(2,'bill'),(3,'david');
SELECT x.*,COUNT(b.passenger_id) bookings
FROM (
SELECT DISTINCT p.passenger_id, p.name, d.destination
FROM passenger p
CROSS JOIN flight d
) x
LEFT JOIN flight d ON d.destination = x.destination
LEFT JOIN booking b ON b.passenger_id = x.passenger_id AND b.flight = d.flight
GROUP BY passenger_id, destination;
-- +--------------+-------+-------------+----------+
-- | passenger_id | name | destination | bookings |
-- +--------------+-------+-------------+----------+
-- | 1 | john | london | 2 |
-- | 1 | john | madrid | 0 |
-- | 1 | john | paris | 1 |
-- | 2 | bill | london | 1 |
-- | 2 | bill | madrid | 0 |
-- | 2 | bill | paris | 0 |
-- | 3 | david | london | 0 |
-- | 3 | david | madrid | 0 |
-- | 3 | david | paris | 0 |
-- +--------------+-------+-------------+----------+