Sql Count Function

Preamble

The number of rows returned by the SELECT operator is determined using the SQL Count Function – COUNT.

Syntax for COUNT function in SQL

SELECT COUNT(aggregate_expression_id)
FROM tabs
[WHERE conds]
[ORDER BY [ ASC | DESC ]];

Or syntax for COUNT function when grouping results by one or more columns.

SELECT expression1_id,2_id, ..._n_id,
COUNT(aggregate_expression_id)
FROM tabs
[WHERE conds]
GROUP BY1_id, 2_id, ..._n_id
[ORDER BY [ ASC | DESC ]];

Parameters or arguments:

• Expressions that are not contained in the COUNT function must be used in the GROUP BY clause at the end of the SQL query, such as expression1_id, expression2_id, etc.
• aggregate_expression_id identifies the column or expression that will be used to calculate non-zero values.
• The tables from which you want the records are listed as tabs; the FROM clause must include at least one table.
• These are the requirements that must be satisfied in order to choose records.
• If more than one expression is specified, the values must be separated by commas. ORDER BY is an optional expression that is used to order the records in the result set.
• If no modifier is specified, the resultant set is sorted in ascending order by using the optional ASC algorithm.
• DESC sorts the result set in descending order of colors; it is optional.

COUNT function only includes NOT NULL values

The COUNT function will only count records where NULL is NOT equal to COUNT(s), which is something that not everyone is aware of. Expressions are excluded from COUNT calculations when they are NULL. Let’s take a closer look at this.

In this example, we have a table with the following data:

Enter the SELECT query that makes use of the COUNT function below.

SELECT COUNT(custom_id)
FROM customs;

1 entry will be selected. These are the outcomes you ought to obtain.

In this example, the query will return 6 because the customer table has 6 records and all custom_id values are NOT NULL (i.e. custom_id is the primary key for the table).

However, what happens if we apply the COUNT function to a NULL value? Enter the following SELECT operator to calculate the fav_website column, which has a NULL-able range of possible values.

SELECT COUNT(fav_website)
FROM customs;

1 entry will be selected. These are the outcomes you ought to obtain.

The second example will return the value 5. Since one of the fav_website values is NULL, the COUNT function will not count it. Consequently, the query will return 5 rather than 6.

Use the primary key when using the COUNT or COUNT(*) functions to ensure that no records are left out of the calculations.

Use a single expression in the COUNT function

Let’s take a look at an illustration of the COUNT function being used in a query with just one expression.

In this example, we have a table with the following data:

Type the SQL query in the box provided.

SELECT COUNT(*) AS total_id
FROM empls
WHERE salary_id > 50,000;

1 entry will be selected. These are the outcomes you ought to obtain.

In this example, we will return the number of employees with salaries over $50,000. To make our query results easier to read, we gave the COUNT(*) nickname total. When we return the result set, the column header will now be the total_id.

Using GROUP BY with COUNT function

In some cases you will need to use the GROUP BY operator with the COUNT function. This happens when the SELECT operator contains columns that are not part of the COUNT function. Let us look at this further.

Again, using a table filled with the following data.

Enter the following SQL statement.

SELECT dept1_id,
COUNT(*) AS total_id
FROM empls
WHERE salary_id > 50,000
GROUP BY dept1_id;

Two entries will be selected. Here are the results that you should get.

In this example, the COUNT function will return the number of employees who earn over $50,000 for each dept1_id. Since the dept1_id column is not included in the COUNT function, it must be specified in the GROUP BY operator.

Using DISTINCT with function COUNT

Did you know that you can use DISTINCT in the COUNT function? This allows only unique values to be calculated.

Using the same table as in the previous example.

Enter the following SQL statement.

SELECT COUNT(DISTINCT dept_id) AS total
FROM employees
WHERE salary > 50,000;
1 entry will be selected. Here are the results that you should get.

In this example, the COUNT function will return a unique number of dept1_id values, in which at least one employee has more than $50,000.

TIP: Configuring performance with the COUNT function
Since the COUNT function will return the same results regardless of which NOT NULL fields are included in the COUNT function parameters (i.e. in brackets), you can use COUNT(1) to improve performance. Now the database kernel will not need to extract any data fields, instead it will just get an integer value of 1.

For example, instead of entering this operator.

SELECT dept1_id,
COUNT(*) AS total_id
FROM empls
WHERE salary_id > 50,000
GROUP BY dept1_id;

You can replace COUNT(*) with COUNT(1) to increase productivity.

SELECT dept1_id,
COUNT(1) AS total_id
FROM empls
WHERE salary_id > 50,000
GROUP BY dept1_id;

Since the COUNT(*) syntax was not used, the COUNT function no longer needs to extract every field from the employees table. Every entry that satisfies your criteria will simply receive a numeric value of 1.

Sql Training Online Sql Count Function

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.