{"id":173545,"date":"2023-11-13T22:12:37","date_gmt":"2023-11-13T21:12:37","guid":{"rendered":"https:\/\/liora.io\/en\/?p=173545"},"modified":"2026-02-06T08:47:17","modified_gmt":"2026-02-06T07:47:17","slug":"count-function-in-sql-full-tutorial-on-this-formula","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/count-function-in-sql-full-tutorial-on-this-formula","title":{"rendered":"COUNT() function in SQL : Full tutorial on this formula"},"content":{"rendered":"

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.<\/strong><\/p>\t\t\n\t\t\t

SQL COUNT() syntax<\/h3>\t\t\n\t\t

There are several ways of using the COUNT function in an SQL query.<\/a> The first is to find out the total number of rows, zero or not:<\/p>\t\t\n\t\t\t

SELECT COUNT(*) FROM table<\/h4>\t\t\n\t\t

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:<\/p>\t\t\n\t\t\t

SELECT COUNT(nom_colonne) FROM table<\/h4>\t\t\n\t\t

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:<\/p>\t\t\n\t\t\t

SELECT COUNT(DISTINCT nom_colonne) FROM table<\/h4>\t\t\n\t\t

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.<\/p>\t\t\n\t\t\t\n

Devenir un expert sur SQL<\/a><\/div><\/div>\n\n\t\t\t

Comment est utilis\u00e9 la fonction COUNT() ? <\/h3>\t\t\n\t\t

Depending on how the COUNT() function is used, the results are different. Find out how to use it, depending on your specific use case.<\/p>\t\t\n\t\t\t

Using COUNT(*)<\/h4>\t\t\n\t\t

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.<\/p>

SELECT COUNT(*)<\/p>

FROM HumanResources.Employees;<\/p>

The results will be :<\/p>

———–<\/p>

490<\/p>

(1 row(s) affected)<\/p>\t\t\n\t\t\t

Using COUNT() with GROUP BY<\/h4>\t\t\n\t\t

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<\/p>

SELECT COUNT(*)<\/p>

FROM HumanResources.Employees;<\/p>

GROUP BY department;<\/p>\t\t\n\t\t\t

Using COUNT() with WHERE<\/h4>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t
<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t

Pour rester dans les exemples de FinaceIncorporation,<\/strong> disons que tu souhaites trouver le nombre de vendeurs qui ont vendu pour plus de 25 000 \u20ac de logiciels. La requ\u00eate prendrait alors la forme suivante :<\/p>

SELECT COUNT(*)<\/pre>
FROM Ventes.Vendeurs<\/p>
WHERE SalesValue &gt ; 25000;<\/p>
Ici, l’instruction WHERE permet d’appliquer un filtre aux enregistrements Where applique un filtre aux enregistrements<\/b>, pr\u00e9cisant ainsi les r\u00e9sultats de ta requ\u00eate. Une autre instruction facultative tr\u00e8s pratique est ORDER BY. Cette expression permet d’afficher les r\u00e9sultats de la requ\u00eate dans un ordre donn\u00e9.<\/i><\/p>\t\t\n\t\t\t
Using COUNT(column)<\/h4>\t\t\n\t\t
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<\/strong> and will not be counted. The query would therefore take the following form:<\/p>
SELECT COUNT(last_purchase_id) FROM user<\/pre>\t\t\n\t\t\t
Is it possible to have several COUNT() in a single SQL query?<\/h3>\t\t\n\t\tDuring a project, you may encounter a situation in which you have to make several calls to a database<\/b>. Nevertheless, this is still time-consuming and for best performance, it’s best to reduce these calls<\/b>. \nRather than making 3 successive requests:\n
SELECT COUNT(*) FROM table1;<\/pre>\nSELECT COUNT(*) FROM table2;\nSELECT COUNT(*) FROM table2 WHERE is_valid = 1;\nYou can group these queries into one<\/b> to return a record containing the values count1, count2 and count3: \n
SELECT<\/pre>\n(SELECT COUNT(*) FROM table1) as count1,\n(SELECT COUNT(*) FROM table2) as count2,\n(SELECT COUNT(*) FROM table2 WHERE is_valid = 1) as count3\nGrouping 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. \t\t\n\t\t\t
Conclusion on the use of COUNT() in SQL<\/h3>\t\t\n\t\t
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.<\/strong><\/p>
If you’d like to find out more about SQL,<\/a> read our full report on the subject.<\/a> Alternatively, learn how to master this relational database query language through one of the training courses by Liora.<\/p>\t\t\n\t\t\t\n
Find out more about SQL training from Liora<\/a><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":76,"featured_media":173547,"comment_status":"open","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"_acf_changed":false,"editor_notices":[],"footnotes":""},"categories":[2433],"class_list":["post-173545","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-ai"],"acf":[],"_links":{"self":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/173545","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/users\/76"}],"replies":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/comments?post=173545"}],"version-history":[{"count":1,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/173545\/revisions"}],"predecessor-version":[{"id":206235,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/173545\/revisions\/206235"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media\/173547"}],"wp:attachment":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media?parent=173545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/categories?post=173545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}