Mega Code Archive
Get the number of orders per customer
<![CDATA[
mysql>
mysql> # create a table called "customers"
mysql> CREATE TABLE IF NOT EXISTS customers
-> ( acc_num INT PRIMARY KEY, name CHAR(20) NOT NULL );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> # insert 3 records into the "customers" table
mysql> INSERT INTO customers (acc_num, name) VALUES (123, "T.Smith");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customers (acc_num, name) VALUES (124, "P.Jones");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customers (acc_num, name) VALUES (125, "H.Nicks");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # create a table called "orders"
mysql> CREATE TABLE IF NOT EXISTS orders
-> ( ord_num INT PRIMARY KEY, acc_num INT NOT NULL );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> # insert 5 records into the "orders" table
mysql> INSERT INTO orders (ord_num, acc_num) VALUES (1, 123);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO orders (ord_num, acc_num) VALUES (2, 124);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO orders (ord_num, acc_num) VALUES (3, 125);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO orders (ord_num, acc_num) VALUES (4, 125);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO orders (ord_num, acc_num) VALUES (5, 123);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # display all data in the "customers" and "orders" tables
mysql> SELECT * FROM customers; SELECT * FROM orders;
+---------+---------+
| acc_num | name |
+---------+---------+
| 123 | T.Smith |
| 124 | P.Jones |
| 125 | H.Nicks |
+---------+---------+
3 rows in set (0.00 sec)
+---------+---------+
| ord_num | acc_num |
+---------+---------+
| 1 | 123 |
| 2 | 124 |
| 3 | 125 |
| 4 | 125 |
| 5 | 123 |
+---------+---------+
5 rows in set (0.00 sec)
mysql>
mysql> # get the number of orders per customer
mysql> SELECT name, customers.acc_num, COUNT(*) AS number_of_orders
-> FROM customers, orders
-> WHERE customers.acc_num = orders.acc_num
-> GROUP BY name
-> ORDER BY customers.acc_num;
+---------+---------+------------------+
| name | acc_num | number_of_orders |
+---------+---------+------------------+
| T.Smith | 123 | 2 |
| P.Jones | 124 | 1 |
| H.Nicks | 125 | 2 |
+---------+---------+------------------+
3 rows in set (0.00 sec)
mysql>
mysql> # delete these sample tables
mysql> DROP TABLE IF EXISTS customers;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS orders;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
]]>