SQL COUNT Function

A Visual tutorial of SQL COUNT Function: Learn to count total rows, non-null values, GROUP BY, HAVING with COUNT - examples and syntax.

What is SQL COUNT function?

To get the count of rows returned by the SELECT statement, use the SQL COUNT function.

SELECT COUNT(*)FROM tbl_employees

SELECT COUNT(emp_name)FROM tbl_employees

SELECT COUNT(DISTINCT emp_name) AS Unique_Employee_Name FROM tbl_employees

SELECT emp_name, COUNT(*) AS total FROM tbl_emp_salary_paid
GROUP BY emp_name;

Where may you use the COUNT function?

  • You may use the COUNT function in the simple SELECT statement
  • The SELECT statement with WHERE clause
  • SELECT with the GROUP BY clause

General way of using the COUNT function

SELECT COUNT(column_name) FROM table_name WHERE condition;
Parameters Description
column_name The column or expression for which you want to count the number of non-null values.
table_name The name of the table from which to count the rows.
condition Optional. It specifies the condition for which the count should be calculated.

Select (*) and Count example

The following example shows using the COUNT function with a simple SELECT statement i.e. without a WHERE clause.

It should return the count of all rows (including Null) in the table; no column is specified:
SELECT COUNT(*)FROM tbl_employees

SQL count all

An example of specifying a column

This example specifies a column name while using the COUNT function in SQL. For that, I used the emp_name column in our tbl_employees table:

SELECT COUNT(emp_name)FROM tbl_employees

SQL count column

The SQL query returned 6 as you can see in the second table in the above graphic.

What if a column contains NULL values?

See the outcome as we use a column that contains NULL:

SELECT COUNT(join_date)FROM tbl_employees

SQL count null

The COUNT returned 5 as one record contains a null value. However, as we fetched the complete table, this record was counted as shown in the first example.

Note: You may apply these and query examples in MySQL as well as in SQL Server.

The example of Count with the DISTINCT clause

The above examples will return the count of the duplicate rows in the table as well. For getting the count of unique records, you may use it with the DISTINCT clause as shown in this example.

I have added another record in the tbl_employees table with the same name i.e. Mike that already exists in the table (for demo only).

Now see what a simple SELECT statement returns as we run the COUNT with the SQL DISTINCT clause for emp_name and emp_id columns.

SELECT COUNT(DISTINCT emp_name) AS Unique_Employee_Name FROM tbl_employees

SELECT COUNT(DISTINCT id) AS Total_Employees FROM tbl_employees

SQL count distinct

The first query returned 6 while the second 7 as the name “Mike” exists twice in the table.

Using GROUP BY clause with COUNT function

For explaining the SQL GROUP BY clause with the COUNT function, I am using another table, tbl_emp_salary_paid, that stores salaries paid to each employee every month (for the demo only).

By using the GROUP BY clause with the COUNT function, we will get the number of times the salary paid to each employee.

The query and result are shown below:

SELECT emp_name, COUNT(*) AS total FROM tbl_emp_salary_paid

GROUP BY emp_name;


SQL count group by

You may compare the actual entries in the tbl_emp_salary_paid table and the second query with the COUNT function.

Adding WHERE clause in COUNT function example

In this query, I have used the WHERE clause to return only those records where the employee’s salary paid is greater than 3000 and then group and return the count as follows:

SELECT emp_name, COUNT(*) AS total FROM tbl_emp_salary_paid

where emp_sal_paid > 3000

GROUP BY emp_name;


SQL count group by where

The example of using COUNT with AVG function

You may also use the COUNT function with other aggregate functions e.g. AVG, SUM, etc.

The following query shows an example by using the same table as in the above example to get the average and total count as follows:

SELECT COUNT(*) As Total, AVG(emp_sal_paid) As Avg_Salary

FROM tbl_emp_salary_paid


SQL count AVG

Using the SUM function with the COUNT example

Now, we will get the sum of the amount paid and the number of times salary is paid by using the SUM and COUNT functions.

The expression applied in the WHERE clause is the amount paid must be greater than 3500:

SELECT COUNT(*) As Total, SUM(emp_sal_paid) As SUM_Salary

FROM tbl_emp_salary_paid 

where emp_sal_paid > 3500


SQL count SUM

The SQL query using COUNT in the HAVING clause

In the following SQL query, we will return the rows containing employee names, the number of times salary paid, and its sum only for those whose salary paid is greater than 3000.

Moreover, the HAVING clause is used with the COUNT function for restricting only those employees who got paid two or more times. Have a look at the query and table shot:

SELECT emp_name, COUNT(*) AS Total, SUM(emp_sal_paid) AS Total_Paid FROM tbl_emp_salary_paid

where emp_sal_paid > 3000

GROUP BY emp_name



SQL count having

You see, only two records qualified this time.