COUNT in SQL

Published : 15 July 2024
Updated : 16 July 2024

133 Views

The Count function are aggregate function that return the number of rows of a SQL query. count is commonly used with the SELECT. This article provides a detailed overview of count aggregate function with example. After reading this article you can use the COUNT function easily and retrieve valuable data from a table.

Syntax

SELECT COUNT(*)
FROM table_name
WHERE condition;

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Demo Database

CREATE TABLE `users` (
   `Id` int PRIMARY KEY AUTO_INCREMENT,
   `Name` varchar(50) NOT NULL,
   `Mobile` varchar(100) DEFAULT NULL,
   `DOB` date DEFAULT NULL,
   `Weight` decimal(10,2) DEFAULT NULL
 );

INSERT INTO Users (Name, Mobile, DOB, Weight)
VALUES ('Deepak', '9999999991', '1993-01-15', 60),
('Amar', '9999999999', '1994-11-20', 85),
('Naveen', '9999999992', '1997-01-15', 55),
('Ramesh', '9999999993', '1998-08-03', null),
('Harsh', '9999999995', '1993-10-05', 73),
('Deepak', '9999999391', '1983-01-15', 90),
('Amar', '9999999969', '1984-11-20', 85),
('Naveen', '9999999492', '1987-01-15', 59),
('Ramesh', '9999999693', '1988-08-03', 86),
('Harsh', '9999998975', '1983-10-05', 70);

Count(*) Example

COUNT(*) aggregate function is used to get the number of rows of the table or SQL query

SQL query to get the number of rows of the Users table.

SELECT COUNT(*) 
FROM Users;

COUNT(*)
10

SQL query to the number of users which name start with `a`.

SELECT COUNT(*)
FROM Users
WHERE name like "a%";

COUNT(*)
2

COUNT(column_name) Example

The COUNT(colum_name) returns the number of rows where the specified column is not null.

SQL query to get the number of users where users weight is set i.e. user Weight is not null.

SELECT COUNT(Weight)
FROM Users;

COUNT(Weight)
9

We can do same with the help of COUNT(*) and WHERE clause

SQL query to get the number of users where users weight is set (i.e. user Weight is not null). with help of count(*) and WHERE clause

SELECT COUNT(*)
FROM Users
WHERE Weight IS NOT NULL;

COUNT(*)
9

COUNT(DISTINCT colum_name) : Ignore Duplicates

You can use the DISTINCT keyword along with the COUNT() function to avoid counting duplicates. When DISTINCT is used, rows with identical values in the specified column are considered as one for counting purposes.

SQL query to get number of unique users name from Users Table.

SELECT COUNT(DISTINCT name)
FROM Users;

COUNT(DISTINCT name)
5

Using an Alias with COUNT

you can provide a column name of counted column using AS keyword.

SELECT COUNT(*) AS [Number of records]
FROM Products;

SQL query to get number of rows in Users Table with counted column alias `RowCount`

SELECT COUNT(*) AS RowCount
FROM Users;
    OR
SELECT COUNT(*) RowCount
FROM Users;

RowCount
10

COUNT with WHERE clause

If you want to retrieve the number of records that satisfied certain condition( business logic). You can use COUNT with WHERE clause.

SQL query to retrieve the number of users whose Weight is greater than 60.

SELECT COUNT(*)
FROM Users
WHERE Weight > 60;

COUNT(*)
6

COUNT with GROUP BY

If you want to retrieve the number of records within groups of data you can use COUNT with GROUP BY.

SQL query that returns the number of users by grouping the results based on the name column.

SELECT name,COUNT(*) as count
FROM Users
GROUP BY name;

name    count
Deepak	2
Amar	2
Naveen	2
Ramesh	2
Harsh	2

Key Characteristics of COUNT functions

COUNT function is used to determine the number of rows in a table or a result set obtained from a query.
COUNT is commonly used with the SELECT.
Using COUNT with GROUP BY allows us to compute counts within groups defined by one or more columns. This is essential for generating aggregated statistics and summaries across various categories or dimensions.
The COUNT functions are crucial functions of SQL for robust data management and analysis for generating reports, creating dashboards, and deriving insights from data stored in SQL databases.
COUNT function handles NULL values when we pass column name in COUNT function then it will returns the number of rows where the specified column is not null.
The COUNT function in SQL can handle duplicates. When the DISTINCT keyword is used with a column name in the COUNT function, it counts the number of unique (non-duplicate) values in that column. Each distinct value is counted only once, regardless of how many times it appears in the table.