## 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 This is a nested IF(): ``` IF(s.amt