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