SQL COUNT Function: Syntax & Practical Examples
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.
Frequently Asked Questions (FAQs)
1. What does the COUNT function do in SQL?
The COUNT function in SQL is an aggregate function used to calculate the number of rows returned by a query. It is commonly used with SELECT to count records in a table or result set and is widely used in reports, dashboards, analytics and validation checks.
2. What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts all rows, including those with NULL values. COUNT(column) counts only rows where the specified column is not NULL. Use COUNT(*) for total records and COUNT(column) when only non-NULL values should be counted.
3. Does COUNT ignore NULL values?
Yes, but only when a column name is used. COUNT(column) ignores NULL values, while COUNT(*) includes rows even if they contain NULLs. This behavior is consistent across major SQL databases.
4. How does COUNT(DISTINCT column) work?
COUNT(DISTINCT column) counts only unique, non-NULL values. Duplicate values are counted once. It is useful for finding unique users, products or categories without inflating results.
5. Can COUNT be used with the WHERE clause?
Yes. WHERE filters rows before COUNT is applied, allowing you to count only records that meet specific conditions. This pattern is extremely common in real-world business queries.
6. How does COUNT work with GROUP BY?
With GROUP BY, COUNT returns the number of rows per group instead of a single total. Each group produces its own count value in the result set.
7. Is COUNT affected by duplicate rows?
Yes. COUNT(*) counts all rows including duplicates, COUNT(column) counts all non-NULL values including duplicates and COUNT(DISTINCT column) counts only unique values. Choosing the right variant is critical for correct results.
8. Can COUNT be used with JOIN queries?
Yes. COUNT is commonly used with JOINs, but joins can multiply rows and inflate counts. COUNT(DISTINCT column) is often used to avoid overcounting in such cases.
9. Does COUNT return multiple rows?
No. COUNT returns a single row unless GROUP BY is used. With GROUP BY, one row is returned per group.
10. Can COUNT be slow on large tables?
COUNT itself is efficient, but performance depends on indexing and query design. COUNT(*) on very large tables may scan many rows. Indexes, filters or summary tables can improve performance.
11. Can COUNT be used in UPDATE or DELETE statements?
Yes. COUNT is often used in subqueries within UPDATE or DELETE statements to apply conditional logic, especially for data cleanup and validation.
12. Is COUNT commonly used in real-world applications?
Yes. COUNT is one of the most frequently used SQL functions in production systems, powering dashboards, pagination, monitoring, reports and analytics.
13. Is COUNT asked in SQL interviews?
Yes. Interviewers often test COUNT(*) vs COUNT(column), NULL handling, GROUP BY usage, DISTINCT behavior and JOIN-related pitfalls.
14. Can COUNT return zero?
Yes. If no rows match the WHERE condition, COUNT returns 0. It never returns NULL, making it reliable for validations.
15. Should I always prefer COUNT(*)?
In most modern databases, COUNT(*) is optimized and safe. Use COUNT(column) only when you intentionally want to exclude NULL values from the count.