SQL - COUNT_BIG() Function

---

---

The COUNT_BIG() and COUNT() functions do the same work. Both return the number of items found in a group. Basically, we can use these functions to find out how many rows are in a table or result set.

The COUNT_BIG() function is used to count the number of items or rows selected by the select statement. We can also pass the condition along with the where clause to count the rows. The only difference between COUNT() function,and the COUNT_BIG() function is that the later returns the value of the type bigint.

This function also allows the use of two optional modifiers ALL and DISTINCT. If we use ALL, this function returns the count of all the values. If we use DISTINCT this function returns the count of unique and non null values.

Syntax

Following is the syntax of the SQL COUNT_BIG() function −

 COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * ) 

Parameters

• expression − an expression of any type. COUNT_BIG() does not allow the use of aggregate functions or subqueries.

• * − It specifies that COUNT_BIG should count all rows to determine the total table row count.

Return Value

The COUNT BIG(*) returns the number of rows in a given table, including duplicate rows. It counts each row individually, including rows with null values.

Example

In the following example, we are counting total records present in the customers table. Using the CREATE statement, we created a table named customers −

 CREATE TABLE customers(ID INT NOT NULL, NAME VARCHAR(30) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(30), SALARY DECIMAL(18, 2)); 

The table stores the ID, NAME, AGE, ADDRESS, and SALARY. Now we are inserting the 7 records in the customers table using the INSERT statement.

 INSERT INTO customers VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00); INSERT INTO customers VALUES(2, 'Khilan', 25, 'Delhi', 1500.00); INSERT INTO customers VALUES(3, 'kaushik', 23, 'Kota', 2000.00); INSERT INTO customers VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00); INSERT INTO customers VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00); INSERT INTO customers VALUES(6, 'Komal', 22, 'MP', 4500.00); INSERT INTO customers VALUES(7, 'Aman', 23, 'Ranchi', null); 

The customers table will be as follows −

 +----+----------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Aman | 23 | Ranchi | NULL | +----+----------+-----+-----------+---------+ 

The following SQL query displays the count of total number of customers −

 SELECT COUNT_BIG(*) AS number_of_customers FROM customers; 

Output

Following is the output of the above SQL query −

 +--------------------+ |number_of_customers | +--------------------+ | 7 | +--------------------+ 

Example

In the following example, we are using the above customer table to demonstrate the use of the COUNT() and COUNT_BIG() functions. Both returns the same output.

 SELECT COUNT(*) AS customer, COUNT_BIG(*) AS customer FROM customers; 

Output

Following is the output of the above SQL query −

 +----------+----------+ | customer | customer | +----------+----------+ | 7 | 7 | +----------+----------+ 

Example

In the following example, we are using the COUNT_BIG() function along with the where clause to count the particular rows. It counts the names whose age is only 23 −

 SELECT COUNT_BIG(NAME) AS same_age FROM customers WHERE AGE = 23; 

Output

Following is the output of the above SQL query that displays the count of names whose age is 23 −

 +-----------+ | same_age | +-----------+ | 2 | +-----------+ 

Example

In the following example, we are counting the ID by using the SQL COUNT_BIG() function along with the DISTINCT operator.

The following SQL query counts the DISTINCT ID−

 SELECT COUNT_BIG(DISTINCT ID) AS DIST_ID FROM customers; 

Output

Following is the output of the above SQL query −

 +----------+ | DIST_ID | +----------+ | 7 | +----------+