How to use COUNT in SQL
Aggregate functions in SQL are used over a set of values, that return a single value. In this article, we will discuss one of the aggregate function COUNT function. Along with the COUNT() function, we will also discuss MIN(), MAX() and AVG() too.
Table of Content
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
What is the COUNT function?
The COUNT function is an aggregate function that is used to find the number of entries in any column except NULL values.
- It is used with SELECT clause
- It can be applied to numeric, text, and date data type
Syntax:
SELECT COUNT ([ALL| DISTINCT] expression | column_name)FROM table_nameWHERE conditionsGROUP BY conditions;
Note: In the above syntax:
ALL: It will count all the values in the table including duplicates
- COUNT function ALL as a default
DISTINCT: It will count only the distinct values that are NOT NULL
COUNT (*): It will return the total number of items including NULL values
Now, let’s discuss some examples to get a better understanding of how to use the count function in SQL
Here, we will use the student table (that contains student id, name, e-mail, percentage, and Department ID)
| Student ID | Name | Percentage(%) | Department ID | |
| 1001 | Ajay | ajay@xyz.com | 85 | 1 |
| 1002 | Babloo | babloo@xyz.com | 67 | 2 |
| 1003 | Chhavi | chhavi@xyz.com | 89 | 3 |
| 1004 | Dheeraj | dheeraj@xyz.com | 75 | 2 |
| 1005 | evina@xyz.com | 91 | 1 | |
| 1006 | Krishna | krishna@xyz.com | 99 | 5 |
Must Read: Introduction to SQL
Must Read: SQL SELECT
Example – 1: Count all the rows in the table
Query
SELECT COUNT(*)FROM Student;
Output
6
Must Read: SQL Comparison and Arithmetic Operator
Must Read: Types of Keys in Database
Example -2 : Count the number of rows in Name column
QUERY
SELECT COUNT(Name)FROM Student;
Output
5
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
Example – 3: Count the distinct number of rows in Department ID
Query
SELECT COUNT (DISTINCT (Department ID))FROM Student;
Output
4
Must Read: SQL ORDER BY
Must Read: SQL WHERE
COUNT with WHERE
Example – 4: Count the number of students who are studying in department id = 1.
Query
SELECT COUNT(*)FROM StudentWHERE Department Id = ‘1’;
Output
2
Must Read: Subqueries in SQL
Must Read: SQL CREATE TABLE
COUNT with GROUP BY
Example – 5: Count the number of students in each department.
SELECT Department ID, COUNT (*)FROM StudentGROUP BY Department ID;
Output
| Department ID | Count(*) |
| 1 | 2 |
| 2 | 2 |
| 3 | 1 |
| 5 | 1 |
Must Read: How to Create, Insert, and Delete SQL views?
Must Read: Introduction to Normalization
SQL MIN/MAX
MIN() – It will return the minimum value in the selected column
MAX() – It will return the maximum value in the selected column
Syntax
SELECT MIN/MAX (column_name)FROM table_name;
Let’s take an example to understand how min and max work in SQL
Example – 6: Find the minimum and maximum percentage in the Student table.
- Minimum
Query
SELECT MIN (Percentage)FROM Student;
Output
99
- Maximum
Query
SELECT MAX(Percentage)FROM (Student);
Output
67
SQL AVG
Average function is also an aggregation function that is used to find the average of the numeric value in the selected columns.
Syntax
SELECT AVG (column_name)FROM table_name;
Example – 7: Find the average percentage of all the students.
Query
SELECT AVG(Percentage)FROM Student;
Output
84.34
Must Read: SQL LIMITS
Must Check: SQL Online Course and Certifications
Conclusion
In this article, we have discussed how to use COUNT function in SQL with examples. We also discussed some other aggregate functions like MIN, MAX and AVG.
Hope this article will help in your Data Science/Data Analysis journey.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
