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 > ; 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?
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.
