SQL Count Function | Count Rows Matching a Criteria

SQL Count is an inbuilt function in SQL Server. It returns the count of the number of rows matching criteria. Also, it can return the count of all the rows in the table if you don’t specify any criteria. In this article, we will discuss the SQL Count Function. Also, we will discuss a few examples of using it.

Syntax

SELECT count(column_name) FROM table_name WHERE condition;

• column_name: The column to count.
• table_name: The table from which to count.
• condition: The condition to specify which rows to count. The function will return the count of only those rows which match the WHERE condition.

Demo Table

For instance, consider the demo table to refer to all the queries in this article.

mysql> SELECT * FROM employees;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

Examples

Let’s discuss a few examples of using the count function in SQL.

Example 1: Count All Rows in Table

For instance, we want to count all the rows in the table. We can use the SQL Count function without specifying anywhere condition. In this case, the function returns the count of all the rows in the table.

mysql> SELECT count(id) FROM employees;
+-----------+
| count(id) |
+-----------+
|         5 |
+-----------+

Example 2: Count Rows Satisfying a Criteria

Similarly, you can specify a WHERE condition in the query. The Count Function in SQL will count only those rows which satisfy the criteria. For instance, let’s count the employees in Alpha Team.

mysql> SELECT count(id) FROM employees WHERE team = 'Alpha';
+-----------+
| count(id) |
+-----------+
|         2 |
+-----------+

Example 3: Count With Limit

However, even if you provide a limit in the query, the function will always return the full count of rows in the table. For example, let’s count all the rows in the table and specify a limit of 2. You should notice that the function will still return the full count.

mysql> SELECT count(1) FROM employees LIMIT 2;
+----------+
| count(1) |
+----------+
|        5 |
+----------+

Note: You can also use 1 or * in column_name of the count function.

Conclusion

In conclusion, we discussed the SQL Count Function in this article. You can read more about it on Wikipedia. Additionally, you can also learn more about SQL on Concatly.