We have the answers to your questions! - Don't miss our next open house about the data universe!

COUNT() function in SQL : Full tutorial on this formula

- Reading Time: 3 minutes
sql count

Do you work with tables and want to know the number of elements in a column or with a filter? The COUNT() aggregation function in SQL can be used to count the number of records in a table. This function is particularly useful if you want to know the number of users online or the number of comments on a blog post. Frequently used by SQL users, find out more about the syntax and use cases of the COUNT() function.

SQL COUNT() syntax

There are several ways of using the COUNT function in an SQL query. The first is to find out the total number of rows, zero or not:

SELECT COUNT(*) FROM table

Next, you can obtain the total number of elements in a particular column. For this function, records with a zero value will not be counted. For a column named “column_name”, here’s the syntax for counting the records:

SELECT COUNT(nom_colonne) FROM table

The last syntax of COUNT allows unique elements to be counted for a column. This means that duplicates do not affect the result of the function for the selected column. For a column named “column_name”, the syntax for counting the number of unique values is as follows:

SELECT COUNT(DISTINCT nom_colonne) FROM table

To optimise the readability of the results, you can filter the rows using GROUP BY. The function returns the number of rows in each group. Note that duplicates and null values are counted.

Comment est utilisé la fonction COUNT() ?

Depending on how the COUNT() function is used, the results are different. Find out how to use it, depending on your specific use case.

Using COUNT(*)

Let’s say you work for a company called FinanceIncorporation. You are looking for the total number of employees in the organisation. This is where the COUNT() function comes in handy.

SELECT COUNT(*)

FROM HumanResources.Employees;

The results will be :

———–

490

(1 row(s) affected)

Using COUNT() with GROUP BY

If your table contains a column called ‘department’ categorising each person by department within the company. By using the GROUP BY clause, you can obtain the number of employees per department. The query would then take the following form

SELECT COUNT(*)

FROM HumanResources.Employees;

GROUP BY department;

Using COUNT() with WHERE

Pour rester dans les exemples de FinaceIncorporation, disons que tu souhaites trouver le nombre de vendeurs qui ont vendu pour plus de 25 000 € de logiciels. La requête prendrait alors la forme suivante :

SELECT COUNT(*)

FROM Ventes.Vendeurs

WHERE SalesValue &gt ; 25000;

Ici, l’instruction WHERE permet d’appliquer un filtre aux enregistrements Where applique un filtre aux enregistrements, précisant ainsi les résultats de ta requête. Une autre instruction facultative très pratique est ORDER BY. Cette expression permet d’afficher les résultats de la requête dans un ordre donné.

Using COUNT(column)

Another method allows you to count the number of registered users on an e-commerce site who have made a purchase. Let’s say you have a “id_achats” table. If there are no purchases, the value is zero and will not be counted. The query would therefore take the following form:

SELECT COUNT(last_purchase_id) FROM user

Is it possible to have several COUNT() in a single SQL query?

During a project, you may encounter a situation in which you have to make several calls to a database. Nevertheless, this is still time-consuming and for best performance, it’s best to reduce these calls. Rather than making 3 successive requests:
SELECT COUNT(*) FROM table1;
SELECT COUNT(*) FROM table2; SELECT COUNT(*) FROM table2 WHERE is_valid = 1; You can group these queries into one to return a record containing the values count1, count2 and count3:
SELECT
(SELECT COUNT(*) FROM table1) as count1, (SELECT COUNT(*) FROM table2) as count2, (SELECT COUNT(*) FROM table2 WHERE is_valid = 1) as count3 Grouping several queries into one is a useful feature when you want to count the number of registered members, logged in, the number of visitors online or even a member’s unread messages and notifications.

Conclusion on the use of COUNT() in SQL

The COUNT() aggregation function is used to count the number of records in a table. Thanks to its syntax with WHERE and COUNT(DISTINCT), it is possible to obtain the number of records with a more precise query, making the result easier to read. Finally, several COUNT() queries can be grouped together to optimise performance and avoid repetitive calls.

If you’d like to find out more about SQL, read our full report on the subject. Alternatively, learn how to master this relational database query language through one of the training courses by DataScientest.

You are not available?

Leave us your e-mail, so that we can send you your new articles when they are published!
icon newsletter

DataNews

Get monthly insider insights from experts directly in your mailbox