Total number of the table vs unique values count : COUNT « Aggregate Functions « SQL / MySQL






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

Total number of the table vs unique values count

        
mysql>
mysql> CREATE TABLE IF NOT EXISTS rings
    -> (
    ->   id             INT             AUTO_INCREMENT PRIMARY KEY,
    ->   stone          CHAR(10)        NOT NULL,
    ->   price          DECIMAL(3,2)    NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO rings (stone, price) VALUES ("Ruby", 40.00);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO rings (stone, price) VALUES ("Emerald", 40.00);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO rings (stone, price) VALUES ("Diamond", 60.00);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO rings (stone, price) VALUES ("Diamond", 50.00);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO rings (stone, price) VALUES ("Garnet", 40.00);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> # display all data in the "rings" table
mysql> SELECT * FROM rings;
+----+---------+-------+
| id | stone   | price |
+----+---------+-------+
|  1 | Ruby    |  9.99 |
|  2 | Emerald |  9.99 |
|  3 | Diamond |  9.99 |
|  4 | Diamond |  9.99 |
|  5 | Garnet  |  9.99 |
+----+---------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> # get the total number of rows
mysql> SELECT COUNT(price) AS num_prices
    -> FROM rings;
+------------+
| num_prices |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql>
mysql> # get the number of unique rows
mysql> SELECT COUNT(DISTINCT price) AS num_distinct_prices
    -> FROM rings;
+---------------------+
| num_distinct_prices |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> # get all the unique stone values
mysql> SELECT DISTINCT stone AS unique_stone_names FROM rings;
+--------------------+
| unique_stone_names |
+--------------------+
| Ruby               |
| Emerald            |
| Diamond            |
| Garnet             |
+--------------------+
4 rows in set (0.00 sec)

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

mysql>








Related examples in the same category

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