Assume that you have a database where you keep sale information such as orders, items so on. There is one-to-many relationship between these two tables. You want to create an invoice per orders by calculating the subtotal on the fly with ROLLUP within a query. This example shows us how it can be done.


Database


Tables


mysql> DESCRIBE orders;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| created_at | datetime | NO | | NULL | |
+------------+------------------+------+-----+---------+----------------+

mysql> DESCRIBE items;
+-----------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | | |
| qty | int(10) unsigned | NO | | NULL | |
| price | decimal(11,2) unsigned | NO | | NULL | |
| orders_id | int(10) unsigned | NO | MUL | NULL | |
+-----------+------------------------+------+-----+---------+----------------+

Data


mysql> SELECT * FROM orders;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2010-01-01 00:00:00 |
| 2 | 2010-02-01 00:00:00 |
+----+---------------------+

mysql> SELECT * FROM items;
+----+----------+-----+----------+-----------+
| id | name | qty | price | orders_id |
+----+----------+-----+----------+-----------+
| 1 | bmw | 1 | 20000.00 | 1 |
| 2 | mercedes | 2 | 25000.00 | 1 |
| 3 | audi | 1 | 19000.00 | 1 |
| 4 | toyota | 3 | 15000.00 | 2 |
| 5 | mazda | 2 | 13000.00 | 2 |
+----+----------+-----+----------+-----------+

Query


mysql> SELECT
-> orders.id AS OrderID,
-> items.name AS ItemName,
-> SUM(items.price) AS ItemPrice,
-> SUM(items.qty) AS ItemQty,
-> SUM(items.qty * items.price) AS OrderTotal
-> FROM orders
-> INNER JOIN items ON orders.id = items.orders_id
-> GROUP BY OrderID, ItemName WITH ROLLUP;

Result


+---------+----------+-----------+---------+------------+
| OrderID | ItemName | ItemPrice | ItemQty | OrderTotal |
+---------+----------+-----------+---------+------------+
| 1 | audi | 19000.00 | 1 | 19000.00 |
| 1 | bmw | 20000.00 | 1 | 20000.00 |
| 1 | mercedes | 25000.00 | 2 | 50000.00 |
| 1 | NULL | 64000.00 | 4 | 89000.00 |

| 2 | mazda | 13000.00 | 2 | 26000.00 |
| 2 | toyota | 15000.00 | 3 | 45000.00 |
| 2 | NULL | 28000.00 | 5 | 71000.00 |

| NULL | NULL | 92000.00 | 9 | 160000.00 |
+---------+----------+-----------+---------+------------+