## Nested, banded aggregation

### from the Artful Common Queries page

 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 That's two nested IF()s ... ```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 %lt;=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 | +--------------+-------+-------------+----------+ ```Last updated 28 Jan 2020

Return to the Artful Common Queries page