Open In App

COUNT() Function in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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

  1. This function finds the number of indexes as returned from the query selected.
  2. This function comes under Numeric Functions.
  3. This function accepts only one parameter namely expression.
  4. 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:

  1. COUNT(*) operator with the SELECT statement. The result set may include duplicate, null, and non-null rows.
  2. COUNT(ALL expression) is used to calculate the total number of rows in the table that are not null rows.
  3. 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:

output2

 

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:

output3

 

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:

output4

 

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:

output6

 

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.


Last Updated : 30 Mar, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads