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?
General way of using the COUNT function
|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.
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.
SELECT COUNT(DISTINCT emp_name) AS Unique_Employee_Name FROM tbl_employees
SELECT COUNT(DISTINCT id) AS Total_Employees FROM tbl_employees
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.