COUNT() Function in SQL Server
Pre-requisites:-Aggregate function in SQL
A Count() function in SQL Server used to finds the number of indexes returned from the query selected.
Features
- This function finds the number of indexes as returned from the query selected.
- This function comes under Numeric Functions.
- This function accepts only one parameter namely expression.
- This function ignores NULL values and doesn’t count them.
There are three ways in which we can use the count function in SQL Server, and they are as follows:
- COUNT(*) operator with the SELECT statement. The result set may 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 distinct number of rows in a table that doesn’t have any null values.
Syntax
COUNT(expression)
Parameter Explanation
- Function: This method accepts only one parameter as given below:
- Expression: A specified expression can be a field or a string-type value.
- Returns: It returns the number of indexes as returned from the query selected.
Now we see various examples on Count(). Suppose we want to count number of columns in a particular table, then we use following query.
Query:
CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50) ); INSERT INTO customers (id, name, age, city) VALUES (1, 'John Doe', 25, 'New York'), (2, 'Jane Smith', 30, 'Los Angeles'), (3, 'Bob Johnson', 35, 'Chicago'), (4, 'Alice Williams', 40, 'Houston'), (5, 'Tom Brown', 45, 'Dallas'), (6, 'Samantha Lee', 50, 'Miami'), (7, 'David Kim', 55, 'San Francisco'), (8, 'Emily Davis', 60, 'Boston'); SELECT COUNT(*) AS num_customers FROM customers;
Output:
Query:
Using the COUNT() function and counting float values.
CREATE TABLE sales ( id INT PRIMARY KEY, product_name VARCHAR(50), price FLOAT ); INSERT INTO sales (id, product_name, price) VALUES (1, 'Product A', 10.5), (2, 'Product B', 15.25), (3, 'Product C', 20.75), (4, 'Product D', 15.25), (5, 'Product E', 10.5); SELECT COUNT(*) AS num_products_A FROM sales WHERE product_name = 'Product A'; SELECT COUNT(*) AS num_products_B FROM sales WHERE price = 15.25;
Output:
Query:
We are using the COUNT() function and getting the output where MRP is greater than the number of counts of MRP.
CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(50), category VARCHAR(50), mrp FLOAT ); INSERT INTO products (id, name, category, mrp) VALUES (1, 'Product A', 'Category 1', 10.5), (2, 'Product B', 'Category 2', 15.25), (3, 'Product C', 'Category 1', 20.75), (4, 'Product D', 'Category 2', 15.25), (5, 'Product E', 'Category 3', 10.5); SELECT * FROM products WHERE mrp > (SELECT COUNT(mrp) FROM products);
Output:
Query:
Using the COUNT() function and getting the records of (MRP-sales price).
CREATE TABLE package ( user_id int IDENTITY(100, 4) NOT NULL, item VARCHAR(10), mrp int, sp int ); INSERT package(item, mrp, sp) VALUES ('book1', 250, 240); INSERT package(item, mrp, sp) VALUES ('book2', 350, 320); INSERT package(item, mrp) VALUES ('book3', 400); SELECT COUNT(mrp-sp) FROM package;
Output:
COUNT Function with HAVING Clause
Query
CREATE TABLE orders ( id INT PRIMARY KEY, product_name VARCHAR(50), quantity INT, order_date DATE ); INSERT INTO orders (id, product_name, quantity, order_date) VALUES (1, 'Product A', 10, '2022-01-01'), (2, 'Product B', 5, '2022-02-01'), (3, 'Product C', 12, '2022-02-15'), (4, 'Product A', 8, '2022-03-01'), (5, 'Product D', 6, '2022-03-15'), (6, 'Product B', 3, '2022-04-01'), (7, 'Product C', 15, '2022-04-15'), (8, 'Product A', 9, '2022-05-01'), (9, 'Product E', 4, '2022-05-15'), (10, 'Product F', 2, '2022-06-01'); SELECT product_name, COUNT(*) AS num_orders FROM orders GROUP BY product_name HAVING COUNT(*) >= 3;
Output:
What Makes COUNT() different from COUNT BIG() ?
The COUNT and COUNT BIG functions in SQL Server count the number of items that are present in the table. A COUNT BIG(), which returns the result as BIGINT data types, and COUNT() returns the result as an INT. Therefore, the COUNT() function will return an error if we try to count the records in a table that contains millions of records, but the COUNT BIG() function will show us the result.