COUNT

Counts the number of rows passed in.

If the value of a given item is NULL, the row is not counted.

May be used as a window function and with the frame clause.

Syntax

Aggregate Function

COUNT ( [DISTINCT] expression )

Window Function & Window Frame Clause

COUNT( [DISTINCT] (col | expr ) OVER (
    [PARTITION BY (col | expr), ...] 
    [ORDER BY (col | expr), ... [ <window_frame> ] ] )

Arguments

  • DISTINCT: optional keyword. If present, will count the number of unique values.

  • expression: any expression. This may be a column name, the result of another function, or a math operation. The special operator "*" is also allowed, to indicate pure row counting.

Return Type

An integer.

Examples

CREATE TABLE count_equipment(
equip_id INT,
equip_type VARCHAR(50),
manufacturer VARCHAR(50),
model VARCHAR(25),
model_number VARCHAR (10),
purchase_yr VARCHAR (4),
quantity INT,
per_item_cost DECIMAL(12,2)
);
INSERT INTO count_equipment values('1100', 'switch', 'Cisco', 'Catalyst', '9500', '2021', '3', '18110.00'),
('2100', 'access point', 'Cisco', 'Catalyst', '9150', '2022', '22', '2095.00'),
('4100', 'switch', 'Juniper', 'EX', '3300', '2019', '42', '450.00'),
('5200', 'server', 'Dell', 'PowerEdge MX', '7000', '2021', '14', '7250.00');

Aggregate Function

SELECT COUNT(*) FROM count_equipment;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
SELECT COUNT(manufacturer) AS company FROM count_equipment;
+---------+
| company |
+---------+
|       4 |
+---------+
SELECT COUNT(DISTINCT manufacturer) AS company FROM count_equipment;
+---------+
| company |
+---------+
|       3 |
+---------+

Window Function

SELECT equip_id, equip_type, quantity,
     COUNT(*) OVER (ORDER BY equip_id) AS
COUNT FROM count_equipment ORDER BY equip_id;
+----------+--------------+----------+-------+
| equip_id | equip_type   | quantity | COUNT |
+----------+--------------+----------+-------+
|     1100 | switch       |        3 |     1 |
|     2100 | access point |       22 |     2 |
|     4100 | switch       |       42 |     3 |
|     5200 | server       |       14 |     4 |
+----------+--------------+----------+-------+

Window Frame Clause

SELECT equip_type, quantity, per_item_cost,
COUNT(*) OVER (ORDER BY quantity RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS total_count FROM count_equipment;
+--------------+----------+---------------+-------------+
| equip_type   | quantity | per_item_cost | total_count |
+--------------+----------+---------------+-------------+
| switch       |        3 |      18110.00 |           1 |
| server       |       14 |       7250.00 |           2 |
| access point |       22 |       2095.00 |           3 |
| switch       |       42 |        450.00 |           4 |
+--------------+----------+---------------+-------------+

Last modified: February 23, 2023

Was this article helpful?