SQL

COUNT() in SQL

🔢 The COUNT() function is an aggregate functionin SQL that returns the number of rows or non- NULL values in a query result. It is one of the most frequently used SQL functions for generating reports, summaries, statistics, and analytical queries.

📖 What is COUNT()?

The COUNT() function counts records in a table or query result. Depending on how it is used, it can count:

📌 All rows in a table.
📌 Non- NULL values in a specific column.
📌 Unique (distinct) values in a column.

Information

COUNT() always returns a numeric value representing the total number of matching records.

📝 Basic Syntax

COUNT() Syntax

SELECT COUNT(expression)
FROM table_name
WHERE condition;

📊 Sample Table

Assume the following Students table:

StudentID	Name	Department	Marks	Email
101	Alice	Computer Science	92	alice@example.com
102	Bob	Mathematics	85	bob@example.com
103	Charlie	Physics	78	NULL
104	David	Computer Science	95	david@example.com
105	Eva	Mathematics	88	NULL

1️⃣ COUNT(*)

COUNT(*) counts every row returned by the query, regardless of whether any column contains NULL values.

Count All Rows

SELECT COUNT(*)
FROM Students;

Result:

COUNT(*)
5

2️⃣ COUNT(ColumnName)

When a column name is specified, COUNT() counts only non-NULL values in that column.

Count Non-NULL Email Addresses

SELECT COUNT(Email)
FROM Students;

Since two students have NULL email addresses, the result is 3.

3️⃣ COUNT(DISTINCT ColumnName)

Use DISTINCT with COUNT() to count only unique values.

Count Unique Departments

SELECT COUNT(DISTINCT Department)
FROM Students;

This query counts the number of different departments instead of counting every row.

🔍 COUNT() with WHERE

The WHERE clause filters rows before they are counted.

Count Computer Science Students

SELECT COUNT(*)
FROM Students
WHERE Department = 'Computer Science';

📊 COUNT() with GROUP BY

The GROUP BY clause groups rows, and COUNT() calculates the number of records in each group.

Count Students by Department

SELECT Department,
       COUNT(*) AS TotalStudents
FROM Students
GROUP BY Department;

Sample Result:

Department	TotalStudents
Computer Science	2
Mathematics	2
Physics	1

📈 COUNT() with HAVING

The HAVING clause filters grouped results after the count has been calculated.

Departments with More Than One Student

SELECT Department,
       COUNT(*) AS TotalStudents
FROM Students
GROUP BY Department
HAVING COUNT(*) > 1;

🔗 COUNT() with JOIN

The COUNT() function is commonly used with joins to count related records.

Count Courses per Student

SELECT s.Name,
       COUNT(e.CourseID) AS TotalCourses
FROM Students s
JOIN Enrollments e
ON s.StudentID = e.StudentID
GROUP BY s.Name;

⚖️ COUNT(*) vs COUNT(Column)

Feature	COUNT(*)	COUNT(Column)
Counts All Rows	✅ Yes	❌ No
Ignores NULL Values	❌ No	✅ Yes
Requires Column Name	❌ No	✅ Yes

📊 Common Uses of COUNT()

Purpose	Example
Total Records	COUNT(*)
Non-NULL Values	COUNT(Email)
Unique Values	COUNT(DISTINCT Department)
Grouped Counts	COUNT(*) GROUP BY Department

💼 Real-World Example

A university administrator wants to know how many students are enrolled in each department.

Department-wise Student Count

SELECT Department,
       COUNT(*) AS StudentCount
FROM Students
GROUP BY Department
ORDER BY StudentCount DESC;

This query produces a summary showing the number of students in each department, sorted from the largest department to the smallest.

⚠️ Best Practices

Best Practice

Use COUNT(*) when counting rows, use COUNT(Column) when you want to ignore NULL values, use COUNT(DISTINCT ...) to count unique values, and combine COUNT() with GROUP BY for reporting and analytical queries.

🚀 Key Points to Remember

📌 COUNT() is an aggregate function that returns the number of records.
📌 COUNT(*) counts every row.
📌 COUNT(Column) counts only non- NULL values.
📌 COUNT(DISTINCT Column) counts unique values.
📌 COUNT() is commonly used with WHERE, GROUP BY, and HAVING.
📌 It is one of the most important functions for SQL reporting and data analysis.

>>"The COUNT() function transforms raw data into meaningful statistics by answering one of the most common questions: 'How many?'"

Summary

✅ The COUNT() function is one of the most essential SQL aggregate functions. Whether counting rows, non- NULL values, unique entries, or grouped records, it provides the foundation for reporting, analytics, and business intelligence queries.