Get the number of woods for each type of item : COUNT « Aggregate Functions « SQL / MySQL






  1. SQL / MySQL
  2. Aggregate Functions
  3. COUNT

Get the number of woods for each type of item

       
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> # get the number of woods for each type of item
mysql> SELECT item, COUNT(*) AS  num_woods
    -> FROM cabinets
    -> GROUP BY item;
+--------------+-----------+
| item         | num_woods |
+--------------+-----------+
| Bookcase     |         3 |
| Display Case |         2 |
+--------------+-----------+
2 rows in set (0.00 sec)

mysql>
mysql> # delete this sample table
mysql> DROP TABLE IF EXISTS cabinets;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.COUNT([DISTINCT] { | *})
2.Get the number of items for each type of wood
3.Counting
4.How many times did drivers travel more than 200 miles in a day?
5.COUNT and GROUP BY
6.Count by group
7.COUNT(expr) doesn't count NULL values is useful when producing multiple counts from the same set of values.
8.The different forms of COUNT( ) can be very useful for counting missing values
9.Put the COUNT( ) expression in a HAVING clause instead.
10.Tracking Down Duplicates
11.Queries counting duplicate records have the following form
12.Counting and Identifying Duplicates
13.Count number of rows containing duplicated names
14.To see which names are duplicated in the cat_mailing table, use a summary query that displays the non-unique v
15.Eliminating Duplicates from a Query Result
16.Removing Duplicates of a Particular Row
17.Count duplicate records
18.How many days did Suzi drive?
19.How many messages were sent by each message sender
20.How many states did the United States consist of at the beginning of the 20th century?
21.Or to count weekend versus weekday trips
22.Count each value and see which one is most common
23.Generating Frequency Distributions
24.Relative frequency distributions
25.Counting Missing Values
26.Count them directly using SUM(ISNULL(score)).
27.Count the total number of rows
28.Categorizing Non-Categorical Data
29.Total number of the table vs unique values count
30.To produce a count-per-author summary that includes even authors with no books in the book table, use a LEFT J