Sql Count Function

Sql Count Function – COUNT is used to calculate the number of rows returned in the SELECT operator.

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:

• expression1_id,2_id,… – Expressions that are not encapsulated in the COUNT function and must be included in the GROUP BY sentence at the end of the SQL query.
• aggregate_expression_id – This is the column or expression whose non-zero values will be taken into account.
• tabs – The tables from which you want the records. The FROM sentence must contain at least one table
• WHERE conds – It’s optional. These are the conditions that must be met to select records.
• ORDER BY – It’s optional. The expression used to sort the records in the result set. If more than one expression is specified, the values must be separated by commas.
• ASC – It’s optional. ASC sorts the resultant set in ascending order by. This is the default behavior if no modifier is specified.
• DESC – It’s optional. DESC sorts the result set in descending order of colors.

COUNT function only includes NOT NULL values

Not everyone understands this, but the COUNT function will only count those records in which NULL is NOT equal to COUNT(s). When expressions is NULL, it is not included in COUNT calculations. Let’s look at this further.

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

Enter the following SELECT query that uses the COUNT function.

SELECT COUNT(custom_id)
FROM customs;

1 entry will be selected. Here are the results that you should get.

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).

But what happens when we encounter a value of NULL using the function COUNT? Let’s enter the following SELECT operator, which will calculate the fav_website column, which may contain values NULL.

SELECT COUNT(fav_website)
FROM customs;

1 entry will be selected. Here are the results that you should get.

In the second example, value 5 will be returned. Since one of the fav_website values is NULL, it will be excluded from the COUNT function calculation. As a result, the query will return 5 instead of 6.

Tip: Use primary key in COUNT or COUNT(*) function if you want to make sure that records are not excluded in the calculations.

Use a single expression in the COUNT function

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

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

Enter the following SQL statement.

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

1 entry will be selected. Here are the results that you should get.

In this example, we will return the number of employees with salaries over $50,000. We have assigned the COUNT(*) nickname total to make our query results more readable. The total_id will now appear as the column header when we return the result set.

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;

The COUNT function now does not need to extract all fields from the employees table as it would have been if the COUNT(*) syntax had been used. It will simply get a numeric value of 1 for each entry that matches your criteria.

Sql Training Online – Sql Count Function