How to use COUNT in SQL

How to use COUNT in SQL

2 mins read1K Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Nov 22, 2022 19:32 IST

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.

2022_07_SQL-COUNT-MINMAX-AVG.jpg

Table of Content

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_name
WHERE conditions
GROUP BY conditions;
Copy code

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 E-mail 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
Student

Must Read: Introduction to SQL

Must Read: SQL SELECT

Example – 1: Count all the rows in the table

Query

 
SELECT COUNT(*)
FROM Student;
Copy code

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;
Copy code

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;
Copy code

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 Student
WHERE Department Id =1;
Copy code

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 Student
GROUP BY Department ID;
Copy code

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;
Copy code

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;
Copy code

Output

99

  • Maximum

Query

 
SELECT MAX(Percentage)
FROM (Student);
Copy code

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;
Copy code

Example – 7: Find the average percentage of all the students.

Query

 
SELECT AVG(Percentage)
FROM Student;
Copy code

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.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

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

Comments