mysql>
mysql> CREATE TABLE BookOrders
-> (
-> OrderID SMALLINT NOT NULL,
-> BookID SMALLINT NOT NULL,
-> Quantity SMALLINT NOT NULL,
-> PRIMARY KEY (OrderID, BookID)
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> INSERT INTO BookOrders VALUES
-> (101, 13331, 1),
-> (101, 12786, 1),
-> (101, 16284, 2),
-> (102, 19354, 1),
-> (102, 15729, 3),
-> (103, 12786, 2), (103, 19264, 1), (103, 13331, 1),
-> (103, 14356, 2), (104, 19354, 1), (105, 15729, 1), (105, 14356, 2),
-> (106, 16284, 2), (106, 13331, 1), (107, 12786, 3), (108, 19354, 1),
-> (108, 16284, 4), (109, 15729, 1), (110, 13331, 2), (110, 12786, 2),
-> (110, 14356, 2), (111, 14356, 2);
Query OK, 22 rows affected (0.00 sec)
Records: 22 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> SELECT OrderID, SUM(Quantity) AS Total
-> FROM BookOrders
-> GROUP BY OrderID
-> HAVING Total>(SELECT AVG(Quantity) FROM BookOrders);
+---------+-------+
| OrderID | Total |
+---------+-------+
| 101 | 4 |
| 102 | 4 |
| 103 | 6 |
| 105 | 3 |
| 106 | 3 |
| 107 | 3 |
| 108 | 5 |
| 110 | 6 |
| 111 | 2 |
+---------+-------+
9 rows in set (0.00 sec)
mysql>
mysql> drop table BookOrders;
Query OK, 0 rows affected (0.00 sec)