COUNT Function in SQL Server – Applications & Parameters

In this tutorial, we will understand in detail the aggregate function Count in SQL. Let us now try to learn more about the count function with examples.

What are the Aggregate Functions in SQL?

Aggregate functions are used over a set of values, which in return comes down to a single value.

Count function in SQL is deterministic in nature i.e. no matter how many times it is run, the answer remains the same until the contents of the table are modified.

Count function doesn’t ignore the NULL values in the database and considers them too while calculating the result of the query.

In SQL we have various Aggregate functions like:

Where do we use Count in SQL?

We use the count function when we need to get the count of something. For example:

1. COUNT(*): Returns the total records in the table.
Syntax:

SELECT col1,
Col2, COUNT(*) AS col_count,
FROM tableName;

2. COUNT(columnName): Returns the total number of records(Includes the Null values also) in the columnName.
Syntax:

SELECT col1,col2,.......,
COUNT(colName) AS col_count 
FROM tableName;

3. COUNT(DISTINCT columnName): Returns the count of unique records in the columnName.
Syntax:

SELECT col1,col2,....,
COUNT(DISTINCT colName) AS colDistinctCount 
FROM tableName;

Demo DataBase

Let us now view our demo database and understand it’s contents.
Query: 

SELECT * FROM DataFlair;

Output:

In our DataFlair database, we have five columns indicating the details of employees at DataFlair. The columns available are as follows:

• Name
• Age
• Employee_id
• Location
• Experience

Let us now work on examples using the DataFlair database and try to understand the Count() function in SQL.

Example 1: Let us find the number of records in our database.
Query:

SELECT COUNT(*) AS Record_count FROM dataflair ;

Output:

Here we can see the number of records available in our database.

Example 2: Let us first count the distinct locations we have our offices at.
Query:

SELECT COUNT(DISTINCT location) AS location_count FROM dataflair ;

Output:

Here we can see the count of unique locations is found by the query.

Example 3: Let us now find the count of employees at each location.
Query:

SELECT location,
COUNT(location) AS location_count 
FROM dataflair 
group by location;

Output:

Here we can see that the query has found the count of employees at each location.

Example 4: Let us now count the employees whose age is more than 25.
Query: 

SELECT COUNT(age) AS ageAbove25 FROM dataflair 
where age>25;

Output:

Here the query returns the count of employees whose age is more than 25.

Example 5: Let us find the number of employees of each age.
Query:

SELECT age,COUNT(age) AS countOfAge FROM dataflair 
group by emp_id;

Output:

Here we can see the count of each age.

Example 6: Let us now find the count of employees with respective experience from maximum to minimum.
Query:

SELECT experience as Exp,
COUNT(experience) AS Total_exp FROM dataflair 
group by experience
order by experience desc;

Output:

Here we can see the count of employees with given experience.

Summary

In this tutorial, we have understood how and when to use SQL count() function. We have understood all the use cases of the count function.

Further, we have seen how to find counts by forming, we have seen how to count the total records and the distinct records. We have discussed various examples based on various use cases.