# 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

```sql
COUNT ( [DISTINCT] expression )
```

## Window Function & Window Frame Clause

```sql
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

```sql
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

```sql
SELECT COUNT(*) FROM count_equipment;


```

```output

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

```

```sql
SELECT COUNT(manufacturer) AS company FROM count_equipment;


```

```output

+---------+
| company |
+---------+
|       4 |
+---------+
```

```sql
SELECT COUNT(DISTINCT manufacturer) AS company FROM count_equipment;


```

```output

+---------+
| company |
+---------+
|       3 |
+---------+
```

## Window Function

```sql
SELECT equip_id, equip_type, quantity,
     COUNT(*) OVER (ORDER BY equip_id) AS 
     COUNT FROM count_equipment ORDER BY equip_id;


```

```output

+----------+--------------+----------+-------+
| 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

```sql
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;


```

```output

+--------------+----------+---------------+-------------+
| 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 |
+--------------+----------+---------------+-------------+

```

***

Modified at: February 23, 2023

Source: [/db/v9.1/reference/sql-reference/aggregate-functions/count/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/count/)

(An index of the documentation is available at /llms.txt)