Thứ Hai, Tháng Hai 10, 2025
spot_img
HomeCOUNT function in SQL Server

COUNT function in SQL Server

COUNT in SQL How is it used? If you don't know the answer, please join Quantrimang.com to find out How to use the COUNT function in SQL hereafter.

SQL Server is an indispensable part of the programming world. If you want to grow in this industry, you should definitely learn SQL Server. Basically, using SQL Server is not difficult, it also includes functions. In the article below, let's learn about the count function in SQL Server!

The COUNT() function in SQL Server is part of the aggregate function used to calculate the total number of rows in a table. When the result has no rows, it returns NULL. The SQL Server COUNT function is often used with the SELECT command and its return type is INT.

COUNT() is a built-in function that accepts a single parameter, which can be a column or a valid expression, and returns a single result that summarizes the group of input data. This function does not ignore NULL values ​​because it also considers them while calculating the results of the query. It can also work with WHERE, GROUP BY, ORDER BY and HAVING to get filtered results.

This article will show you in detail how to use the COUNT() number processing function in SQL Server with syntax and specific examples to easily visualize and grasp the function better.

Describe the COUNT SQL Server function

COUNT function in SQL Server used to count the number of records (data, rows) in a data table. NULL values ​​are ignored.

SQL Server COUNT function syntax

To use the COUNT function in SQL Server, we use the following syntax:

SELECT COUNT(cot)
  FROM bang
  [WHERE dieukien];

Parameter:

  • cot: columns or calculated values ​​or expressions that you want to count
  • state: table used to retrieve records. There must be at least 1 table in the FROM clause.
  • dieukien: option. Conditions that a record must meet to be selected.

Note:

  • The COUNT function can be used in the following versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

Example of SQL Server COUNT function

Take a look and explore some examples of the COUNT function in SQL Server.

Suppose, we have a data table as follows:

Data table

Example 1: Count the number of categories in the table

SELECT COUNT(*)
  FROM Quantrimang
  WHERE Sobai > 100;

Returned results:

Result: 5

In this example, we count the number of categories in the Quantrimang table where the number of articles is greater than 100.

Example 2: Using DISTINCT

The DISTINCT and COUNT commands can be used together to count the number of non-duplicate results.

SELECT COUNT(DISTINCT Sobai)
 FROM Quantrimang
 WHERE Sobai > 100;

Returned results:

Result: 4

This example uses the DISTINCT keyword so repeated values ​​are only counted once. In the given data table, the value “101” appears twice, but is only counted once, so the total number of rows counted will be 4.

Example 3: Using GROUP BY

The following example counts all records related to a large category and you would do the following:

SELECT Chuyenmuclon, COUNT(Chuyenmuccon) AS "So luong"
  FROM Quantrimang
  GROUP BY Chuyenmuclon;

Returned result:>

+----------------+----------+
| Chuyenmuclon   | So luong |
+----------------+----------+
|Laptrinh        |    3     |
|Mang xa hoi     |    2     |
|Trinh duyet web |    1     |
+----------------+----------+

COUNT function with OVER()

The OVER() command determines which rows from the query are applied to this function, the order in which the function evaluates them, and when the function's calculations need to be restarted. The query below provides the total number of employees for each occupation in the employee_info table and sorts the results in ascending order:

SELECT DISTINCT occupation,
COUNT(name) OVER (PARTITION BY occupation) AS "Total Employee"
FROM employee_info
ORDER BY "Total Employee";

Results received:

The COUNT function in SQL Server is used with Over

Difference between COUNT() and COUNT_BIG() function

The COUNT and COUNT_BIG functions in SQL Server basically have the same function: they both count the number of entries in the table. However, sometimes, you need to make the right choice for the application you are developing. In that case, you should make the most suitable choice for your app based on the data type.

The COUNT function returns an INT (integer), while the COUNT_BIG() function returns a BIGINT data type. So, if there is a table with millions of records and need to count the number of records. The COUNT() function will return an error, while COUNT_BIG() will display the same result.

The other main difference between them is that while creating an index, the COUNT() function does not allow creating a clustered index on the viewer. However, you can create a grouped index using the COUNT_BIG() function.

In short, although there is not much difference, COUNT() and COUNT_BIG() still have their own functions in some cases. Knowing this information, you will know how to use them correctly.

Basically, you need to remember the following when using the count command in SQL Server:

The SQL Server Count() function is used to find the number of indexes returned from the selected query.

Main characteristics:

  • This function finds the number of indexes returned from the selected query.
  • This function is in the functions.
  • This function accepts only one parameter, an expression.
  • This function ignores NULL values ​​and does not count them.

There are three ways you can use the count statement in SQL Server:

  • COUNT operator
  • with the SELECT command. The result set can include: duplicate, null, and non-null rows.
  • COUNT(ALL expression) is used to calculate the total number of rows in the table that are not null rows.

The COUNT(DISTINCT expression) function only counts the number of distinct rows in a table that do not have any null values.

Previous article: CEILING function in SQL Server

Next article: FLOOR function in SQL Server

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments