COUNT_BIG Function in SQL Server

In this SQL Server tutorial, you will learn about the COUNT_BIG function in SQL Server.

First, you will understand the COUNT_BIG() function, then the difference between the COUNT() and COUNT_BIG() functions. After that, the syntax of the COUNT_BIG() function.

Then, you will learn how to use the COUNT_BIG() function with duplicate and unique table column values.

Finally, you will understand how to use the COUNT_BIG() function with the HAVING clause.

Table of Contents

COUNT_BIG Function in SQL Server

The COUNT_BIG function in SQL Server counts the number of rows or values in a table, group or result set. This function behaves in the same way as the COUNT function in SQL Server.

The difference lies in how these two functions return the value, which means the data type of the return value. So COUNT_BIG() returns a BIGINT data type, and COUNT() returns the INT data type.

The syntax is given below.

COUNT_BIG( ALL | DISTINCT expression | * } )

COUNT_BIG(): This function helps determine the number of rows in the database table.
ALL | DISTINCT expression | * : You can use the COUNT_BIG() in three ways as shown below:
- First, understand the expression; it can be a column name or any image, text, or next. However, aggregate functions or subqueries are not allowed in the expression.
- Second, you can use the COUNT_BIG() function as COUNT_BIG(ALL expression), COUNT_BIG(DISTINCT expression), and COUNT_BIG(*).
  - COUNT_BIG(ALL expression) returns all non-null expression values (column_name).
  - COUNT_BIG(DISTINCT expression) returns unique non-null expression values (column_name).
  - COUNT_BIG(*) returns all rows in a table with NULL and duplicate values in any column.

Let’s understand with different examples. You have the table FinancialInfo as shown below.

COUNT_BIG Function in SQL Server Financialinfo Tables

The COUNT(*) to count the number of rows in the above table; for that, use the query below.

SELECT COUNT_BIG(*) AS NumOfRows FROM FinancialInfo;

The number of rows in a table FinancialInfo is 20. This count is returned by the COUNT_BIG(*) function on the table; remember, you can use options such as DISTINCT with this function.

This is how you can use the COUNT_BIG function in SQL Server to count the rows in a table.

Next, let’s see how to use the COUNT_BIG() function with ALL and DISTINCT options.

COUNT_BIG Function in SQL Server with ALL and DISTINCT

Your table contains duplicate values in a column; sometimes, you may need to use the COUNT_BIG() function on the duplicate values and sometimes on the unique ones.

To change the behaviour of COUNT_BIG() for the duplicate or unique values, you can use the options ALL (for duplicate) and DISTINCT (for unique).

In simpler words, when considering duplicate values while using the COUNT_BIG function in SQL Server, use the ALL option, as shown in the syntax below.

COUNT_BIG(ALL column_name)

To use the COUNT_BIG() function on unique values of the column, use the syntax below.

COUNT_BIG(DISTINCT column_name)

Let’s take an example and see how to use these options with the COUNT_BIG() function.

For example, you have the Product_Sales table, as shown below.

COUNT_BIG Function in SQL Server with ALL and DISTINCT with Product_Sales Table

The SalePrice column contains the duplicate values, as shown in the above picture. Use the query below to count the values in a SalePrice column.

SELECT COUNT_BIG(ALL SalePrice) AS ColumnDuplicateValues
FROM Product_Sales;

COUNT_BIG Function in SQL Server with ALL Option

The number of values in the SalePrice column is 10, including the duplicate values; here, we are using the ALL option with the COUNT_BIG() function to specify that while counting the values, we also consider the duplicate values of the column.

Next, let’s use the DISTINCT option to count the number of values in a column excluding the duplicate value, so for that, use the query below.

SELECT COUNT_BIG(DISTINCT SalePrice) AS ColumnDuplicateValues
FROM Product_Sales;

COUNT_BIG Function in SQL Server with DISTINCT Option

As you can see from the output, the result set contains 9, which is the number of unique values in a SalePrice column. It counts unique values of the column because the DISTINCT option is used with the COUNT_BIG() function.

Look how the behaviour of the COUNT_BIG() function changes whenever the option ALL or DISTINCT is used. So this is how to use the options ALL and DISTINCT with the COUNT_BIG() function for duplicate and unique values, respectively.

COUNT_BIG Function in SQL Server with HAVING Clause

You can use the COUNT_BIG() function with the HAVING clause to filter the group of rows based on the conditions.

For example, as shown below, consider the Sales table with columns sale_id, product_id, sale_amount and sale_date.

COUNT_BIG Function in SQL Server with HAVING Clause Sales Table

Using the above table data, you must group the table by product_id and find the number of sale amounts greater than 2. For that, use the query below.

SELECT product_id, COUNT_BIG(sale_amount) AS NumberofSalesAmount
FROM Sales
GROUP BY product_id
HAVING COUNT_BIG(sale_amount) > 2;

COUNT_BIG Function in SQL Server with HAVING Clause

From the output, in the result set, product_id with P100, P101, P102, and P103 has many sales amounts greater than 2.

Here in the query part, HAVING COUNT_BIG(sale_amount) > 2, COUNT_BIG() function is used to filter the number of sales amounts greater than 2 in each product_id, which means it counts the number of the sale amounts in the group and returns the group which has many sales amounts greater than 2.

This is how to use the COUNT_BIG function in SQL Server with HAVING clause.

Conclusion

In this SQL Server tutorial, you have learned how to use the COUNT_BIG function in SQL Server to count the number of rows in a table or values in a column. Then, you used the COUNT_BIG() function to count the values in a column containing duplicate and unique values.

Ultimately, you have used the COUNT_BIG function in SQL Server with the HAVING clause to filter the number of sale amounts in group data.