Skip to main content

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 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 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 equipment;

****
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+

SELECT COUNT(manufacturer) AS company FROM equipment;

****
+---------+
| company |
+---------+
|       4 |
+---------+

SELECT COUNT(DISTINCT manufacturer) AS company FROM equipment;

****
+---------+
| company |
+---------+
|       3 |
+---------+

Window Function

SELECT equip_id, equip_type, quantity,
     COUNT(*) OVER (ORDER BY equip_id) AS 
     COUNT FROM 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 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 |
+--------------+----------+---------------+-------------+