COUNT

12min
This article will guide you on how to count the number of rows using the COUNT() function.
OverviewThe COUNT() function allows you to get the number of records that match a specific condition.
The input and return types we support can be seen in the table below.
﻿
The output will return the number of rows in a table regardless of the input types.
ExamplesIn this example, we will use an orders table that stores details of the purchase transactions:
Create a Table
|
CREATE TABLE orders (
    orderid int,
    custname text,
    orderproduct text,
    ordertotal real
);
INSERT INTO orders (orderid, custname, orderproduct, ordertotal)
VALUES 
(9557411, 'Maya', 'Jeans', 10.5),
(9557421, 'Aaron', 'T-Shirt', 9.2),
(9557451, 'Alex', 'Hat', 10.8),
(9557311, 'Will', 'Hat', 8.5),
(9557321, 'Will', 'T-Shirt', 12.15),
(9557351, 'Maya', 'T-Shirt', 9.5),
(9557221, 'Maya', 'Jeans', 11.02),
(9557251, 'Alex', 'Jeans', 11.09),
(9557231, 'Aaron', 'Hat', 14.56),
(9557281, 'Aaron', 'Hat', 12.15),
(9557291, 'Will', 'T-Shirt', 13.1);
CREATE TABLE orders (
    orderid int,
    custname text,
    orderproduct text,
    ordertotal real
);
INSERT INTO orders (orderid, custname, orderproduct, ordertotal)
VALUES 
(9557411, 'Maya', 'Jeans', 10.5),
(9557421, 'Aaron', 'T-Shirt', 9.2),
(9557451, 'Alex', 'Hat', 10.8),
(9557311, 'Will', 'Hat', 8.5),
(9557321, 'Will', 'T-Shirt', 12.15),
(9557351, 'Maya', 'T-Shirt', 9.5),
(9557221, 'Maya', 'Jeans', 11.02),
(9557251, 'Alex', 'Jeans', 11.09),
(9557231, 'Aaron', 'Hat', 14.56),
(9557281, 'Aaron', 'Hat', 12.15),
(9557291, 'Will', 'T-Shirt', 13.1);
﻿
Display the Table
|
SELECT * FROM orders;
SELECT * FROM orders;
﻿
The above query will show the following table:
The orders Table
|
+----------+-----------+---------------+-------------+
| orderid  | custname  | orderproduct  | ordertotal  |
+----------+-----------+---------------+-------------+
| 9557411  | Maya      | Jeans         | 10.5        |
| 9557421  | Aaron     | T-Shirt       | 9.2         |
| 9557451  | Alex      | Hat           | 10.8        |
| 9557311  | Will      | Hat           | 8.5         |
| 9557321  | Will      | T-Shirt       | 12.15       |
| 9557351  | Maya      | T-Shirt       | 9.5         |
| 9557221  | Maya      | Jeans         | 11.02       |
| 9557251  | Alex      | Jeans         | 11.09       |
| 9557231  | Aaron     | Hat           | 14.56       |
| 9557281  | Aaron     | Hat           | 12.15       |
| 9557291  | Will      | T-Shirt       | 13.1        |
+----------+-----------+---------------+-------------+
+----------+-----------+---------------+-------------+
| orderid  | custname  | orderproduct  | ordertotal  |
+----------+-----------+---------------+-------------+
| 9557411  | Maya      | Jeans         | 10.5        |
| 9557421  | Aaron     | T-Shirt       | 9.2         |
| 9557451  | Alex      | Hat           | 10.8        |
| 9557311  | Will      | Hat           | 8.5         |
| 9557321  | Will      | T-Shirt       | 12.15       |
| 9557351  | Maya      | T-Shirt       | 9.5         |
| 9557221  | Maya      | Jeans         | 11.02       |
| 9557251  | Alex      | Jeans         | 11.09       |
| 9557231  | Aaron     | Hat           | 14.56       |
| 9557281  | Aaron     | Hat           | 12.15       |
| 9557291  | Will      | T-Shirt       | 13.1        |
+----------+-----------+---------------+-------------+
﻿
#Case 1: COUNT() with a single expressionThe following example will return the number of all orders in the orders table:
Select count
|
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM orders;
﻿
The final result will be as follows:
Output
|
+-------+
| count |
+-------+
| 11    |
+-------+
+-------+
| count |
+-------+
| 11    |
+-------+
﻿
#Case 2: COUNT() with a GROUP BY clauseThis example will combine the COUNT() function and the GROUP BY clause.
The GROUP BY clause groups the orders based on the customer’s name.
The COUNT() function counts the orders for each customer.
Select count
|
SELECT custname, COUNT (orderid)
FROM orders
GROUP BY custname;
SELECT custname, COUNT (orderid)
FROM orders
GROUP BY custname;
﻿
It will display the output as shown below:
Output
|
+-----------+--------+
| custname  | count  |
+-----------+--------+
| Aaron     | 3      |
| Alex      | 2      |
| Will      | 3      |
| Maya      | 3      |
+-----------+--------+
+-----------+--------+
| custname  | count  |
+-----------+--------+
| Aaron     | 3      |
| Alex      | 2      |
| Will      | 3      |
| Maya      | 3      |
+-----------+--------+
﻿
#Case 3: COUNT() with a HAVING clauseIn this example, we combine the COUNT() function and the HAVING clause to apply a specific condition to find customers who have made more than two orders:
Select count
|
SELECT custname, COUNT (orderid)
FROM orders
GROUP BY custname
HAVING COUNT (orderid) > 2;
SELECT custname, COUNT (orderid)
FROM orders
GROUP BY custname
HAVING COUNT (orderid) > 2;
﻿
The GROUP BY clause groups the orders based on the customer’s name.
The HAVING clause will filter only customers with more than two order IDs.
The COUNT() function counts the orders for each customer.
Output
|
+-----------+--------+
| custname  | count  |
+-----------+--------+
| Aaron     | 3      |
| Will      | 3      |
| Maya      | 3      |
+-----------+--------+
+-----------+--------+
| custname  | count  |
+-----------+--------+
| Aaron     | 3      |
| Will      | 3      |
| Maya      | 3      |
+-----------+--------+
﻿
﻿
Updated 11 May 2023
Did this page help you?
Yes
ABS
BOOL_AND
Docs powered by archbee
TABLE OF CONTENTS
Overview
Examples
#Case 1: COUNT() with a single expression
#Case 2: COUNT() with a GROUP BY clause
#Case 3: COUNT() with a HAVING clause