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;

General way of using the COUNT function

Select (*) and Count example

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

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:

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:

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.

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.

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;

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;

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

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

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

HAVING COUNT(*)>=2

You see, only two records qualified this time.