Get the number of items for each type of wood
mysql> CREATE TABLE IF NOT EXISTS cabinets
-> (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> wood CHAR(10) NOT NULL,
-> item CHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO cabinets (wood, item) VALUES ("Pine", "Bookcase");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO cabinets (wood, item) VALUES ("Beech", "Bookcase");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO cabinets (wood, item) VALUES ("Oak", "Bookcase");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO cabinets (wood, item) VALUES ("Pine", "Display Case");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO cabinets (wood, item) VALUES ("Oak", "Display Case");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM cabinets;
+----+-------+--------------+
| id | wood | item |
+----+-------+--------------+
| 1 | Pine | Bookcase |
| 2 | Beech | Bookcase |
| 3 | Oak | Bookcase |
| 4 | Pine | Display Case |
| 5 | Oak | Display Case |
+----+-------+--------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT wood, COUNT(*) AS num_items
-> FROM cabinets
-> GROUP BY wood;
+-------+-----------+
| wood | num_items |
+-------+-----------+
| Beech | 1 |
| Oak | 2 |
| Pine | 2 |
+-------+-----------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> # delete this sample table
mysql> DROP TABLE IF EXISTS cabinets;
Query OK, 0 rows affected (0.00 sec)