What is SQL COUNT function?

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

Where may you use COUNT function?

  • You may use the COUNT function in simple select statement
  • The select statement with where clause
  • Select with the group by clause

Select (*) and Count example

The following example shows using the count function with simple select statement i.e. without Where clause. It should return the count of all rows 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 as 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 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 complete table, this record was counted as shown in the first example.

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

The example of Count with 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 same name i.e. Mike that already exists in the table (for demo only). Now see what simple select statement returns and as we run the Count with the Distinct clause for emp_name and emp_id columns.

 

SQL count distinct

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

Using Group By clause with Count

For explaining group by clause with 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 Group by clause with Count function, we will get the number of times salary paid to each employee. The query and result are shown below:

 

SQL count group by

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

Adding where clause in COUNT function example

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

 

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 above example to get the average and total count as follows:

 

SQL count AVG

Using SUM function with COUNT example

Now, we will get the sum of the amount paid and number of times salary paid by using the SUM and COUNT functions. The expression applied in the where clause is amount paid must be greater than 3500:

 

SQL count SUM

The SQL query using Count in 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 which salary paid is greater than 3000.

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

 

SQL count having

You see, only two records qualified this time.