Basic about COUNT Function – using and example

This SQL tutorial explains how to use the SQL COUNT function with syntax, examples, and practice exercises.

Description

The SQL COUNT characteristic is used to matter the quantity of rows back in a SELECT statement.

Syntax

The syntax for the COUNT function in SQL is:

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

OR the syntax for the COUNT feature when grouping the effects via one or more columns is:

SELECT expression1, expression2, ... expression_n,
       COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

expression1, expression2, … expression_n Expressions that are no longer encapsulated within the COUNT feature and ought to be included in the GROUP BY clause at the give up of the SQL statement. aggregate_expression This is the column or expression whose non-null values will be counted. tables The tables that you wish to retrieve files from. There must be at least one desk listed in the FROM clause. WHERE conditions Optional. These are stipulations that should be met for the records to be selected. ORDER BY expression Optional. The expression used to type the records in the end result set. If more than one expression is provided, the values should be comma separated. ASC Optional. ASC varieties the end result set in ascending order through expression. This is the default behavior, if no modifier is provider. DESC Optional. DESC kinds the end result set in descending order by way of expression.

DDL/DML for Examples

If you desire to observe along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then strive the examples in your very own database!

Get DDL/DML

Example – COUNT Function solely includes NOT NULL Values

Not everyone realizes this, however the COUNT feature will only be counted the records where the expression is NOT NULL in COUNT(expression). When the expression is a NULL value, it is no longer covered in the COUNT calculations. Let’s discover this further.

In this example, we have a desk known as clients with the following data:

customer_id last_name first_name favorite_website 4000 Jackson Joe techonthenet.com 5000 Smith Jane digminecraft.com 6000 Ferguson Samantha bigactivities.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.com

Enter the following SELECT statement that makes use of the COUNT function:

Try It

SELECT COUNT(customer_id)
FROM customers;

There will be 1 report selected. These are the effects that you see:

COUNT(customer_id)

6

In this example, the query will return 6 considering that there are 6 documents in the clients table and all customer_id values are NOT NULL (ie: customer_id is the most important key for the table).

But what happens when we come across a NULL value with the COUNT function? Let’s enter this next SELECT declaration that counts the favorite_website column which can incorporate NULL values:

Try It

SELECT COUNT(favorite_website)
FROM customers;

There will be 1 record selected. These are the results that you should see:

COUNT(favorite_website)

5

This 2nd example will return 5. Because one of the favorite_website values is NULL, it would be excluded from the COUNT characteristic calculation. As a result, the question will return 5 alternatively of 6.

TIP: Use the primary key in the COUNT function or COUNT(*) if you desire to be positive that data are not excluded in the calculations.

Example – Using a Single Expression in the COUNT Function

Let’s seem to be at an example that shows how to use the COUNT characteristic with a single expression in a query.

In this example, we have a desk known as employees with the following data:

employee_number last_name first_name salary dept_id 1001 Smith John 62000 500 1002 Anderson Jane 57500 500 1003 Everest Brad 71000 501 1004 Horvath Jack 42000 501

Enter the following SQL statement:

Try It

SELECT COUNT(*) AS total
FROM employees
WHERE salary > 50000;

There will be 1 file selected. These are the consequences that you have to see:

total

3

In this example, we will return the wide variety of personnel who have a revenue above $50,000. We’ve aliased the COUNT(*) as total to make our query results greater readable. Now, complete will display as the column heading when the result set is returned.

Example – Using GROUP BY with the COUNT Function

In some cases, you will be required to use the GROUP BY clause with the COUNT function. This happens when you have columns listed in the SELECT assertion that are no longer phase of the COUNT function. Let’s explore this further.

Again, using the employees desk populated with the following data:

employee_number last_name first_name revenue dept_id 1001 Smith John 62000 500 1002 Anderson Jane 57500 500 1003 Everest Brad 71000 501 1004 Horvath Jack 42000 501

Enter the following SQL statement:

Try It

SELECT dept_id, COUNT(*) AS total
FROM employees
WHERE salary > 50000
GROUP BY dept_id;

There will be 2 data selected. These are the consequences that you have to see:

dept_id total

500 2

501 1

In this example, the COUNT characteristic will return the quantity of personnel that make over $50,000 for each dept_id. Because the dept_id column is not included in the COUNT function, it should be listed in the GROUP BY clause.

Example – Using DISTINCT with the COUNT Function

Did you understand that you can use the DISTINCT clause within the COUNT function? This allows you to count only the special values.

Using the same personnel desk as the previous example:

employee_number last_name first_name revenue dept_id 1001 Smith John 62000 500 1002 Anderson Jane 57500 500 1003 Everest Brad 71000 501 1004 Horvath Jack 42000 501

Enter the following SQL statement:

Try It

SELECT COUNT(DISTINCT dept_id) AS total
FROM employees
WHERE salary > 50000;

There will be 1 document selected. These are the outcomes that you must see:

total

2

In this example, the COUNT feature will return the special quantity of dept_id values that have at least one employee that makes over $50,000.

TIP: Performance Tuning with the COUNT Function

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT feature parameters (ie: within the parentheses), you can use COUNT(1) to get better performance. Now the database engine will not have to fetch any data fields, alternatively it will simply retrieve the integer fee of 1.

For example, instead of entering this statement:

Try It

SELECT dept_id, COUNT(*) AS total
FROM employees
WHERE salary > 50000
GROUP BY dept_id;

You may want to change COUNT(*) with COUNT(1) to get higher performance:

Try It

SELECT dept_id, COUNT(1) AS total
FROM employees
WHERE salary > 50000
GROUP BY dept_id;

Now, the COUNT characteristic does now not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will basically retrieve the numeric cost of 1 for each file that meets your criteria.

Practice Exercises

If you choose to take a look at your capabilities using the SQL COUNT function, attempt some of our practice exercises.

These exercises allow you to try out your capabilities with the COUNT function. You will be given questions that you need to solve. After each exercise, we grant the solution so you can take a look at your answer. Give it a try!

Go to Practice Exercises