COUNT()

Example Schema and Data

All of the examples are based on the inventory table:

CREATE TABLE inventory (
  item VARCHAR(20),
  category VARCHAR(20),
  count INT
);

INSERT INTO inventory VALUES
  ('Foo','in stock', 42), ('Foo','back order', 50),
  ('Bar','in stock', 10), ('Bar','back order', 60),
  ('Baz','in stock', 99), ('Baz','back order', 10),
  ('FooBar','in stock', 10), ('FooBar','back order', NULL),
  ('BarBaz','in stock', NULL), ('BarBaz','back order', NULL);

ALL Clause (default behavior)

The ALL clause is the default behavior for COUNT(), and is applied when DISTINCT clause is not specified. In this example, all values are used to compute the count.

The values to be counted:

SELECT item
 FROM inventory;

+--------+
| item   |
+--------+
| Foo    |
| Foo    |
| Bar    |
| Bar    |
| Baz    |
| Baz    |
| FooBar |
| FooBar |
| BarBaz |
| BarBaz |
+--------+

The result of counting these items can be requested with or without the "ALL" clause:

SELECT COUNT(ALL item)
 FROM inventory;

+-----------------+
| COUNT(ALL item) |
+-----------------+
|              10 |
+-----------------+

DISTINCT Clause

Using COUNT() with the DISTINCT clause ignores duplicate values, so each value is counted only once when computing the count. In this example, all distinct item values are used to compute the count:

SELECT COUNT(DISTINCT item)
 FROM inventory;

+----------------------+
| COUNT(DISTINCT item) |
+----------------------+
|                    5 |
+----------------------+

With GROUP BY

Using COUNT() with GROUP BY creates a separate count for each group. In this example, GROUP BY category creates a separate count for each unique category:

SELECT category, COUNT(item)
  FROM inventory
  GROUP BY category ORDER BY category;

+------------+-------------+
| category   | COUNT(item) |
+------------+-------------+
| back order |           5 |
| in stock   |           5 |
+------------+-------------+

With NULL Values

When COUNT() acts on NULL values, those values are not included when computing the count. In this example, some rows in the data set contain NULL values in the count column:

SELECT *
 FROM inventory
 WHERE item = 'FooBar'
 ORDER BY count;

+--------+------------+-------+
| item   | category   | count |
+--------+------------+-------+
| FooBar | back order |  NULL |
| FooBar | in stock   |    10 |
+--------+------------+-------+

The following example computes a count of the count column over the whole inventory for the FooBar item in any category:

SELECT COUNT(count)
 FROM inventory
 WHERE item = 'FooBar';

+--------------+
| COUNT(count) |
+--------------+
|            1 |
+--------------+

With Only NULL Values

When COUNT() is acting only on NULL values, COUNT() returns 0. In this example, all rows in the data set contain NULL values in the count column:

SELECT *
 FROM inventory
 WHERE item = 'BarBaz'
 ORDER BY count;

+--------+------------+-------+
| item   | category   | count |
+--------+------------+-------+
| BarBaz | in stock   |  NULL |
| BarBaz | back order |  NULL |
+--------+------------+-------+

The following example computes a count of the count column over the whole inventory for the BarBaz item in any category:

SELECT COUNT(count)
 FROM inventory
 WHERE item = 'BarBaz';

+--------------+
| COUNT(count) |
+--------------+
|            0 |
+--------------+

COUNT(*)

COUNT(*) returns the total number of rows in the aggregation:

SELECT COUNT(*) from inventory;

+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+