COUNT
On this page
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) ASCOUNT 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