How to use COUNT in SQL

# How to use COUNT in SQL

clickHere
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.

## 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;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)

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: 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

## 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’;Copy code`

Output

2

## COUNT with GROUP BY

Example – 5: Count the number of students in each department.

` `
`SELECT Department ID, COUNT (*)FROM StudentGROUP BY Department ID;Copy code`

Output

## 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 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.