COUNT Function in SQL Server – Applications & Parameters

We offer you a brighter future with industry-ready online courses - Start Now!!

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:

 Sr.No Function Description 1 AVG() Returns the average value. 2 COUNT() Returns the count. 3 MAX() Returns the maximum out of the given values. 4 MIN() Returns the minimum out of the given values. 5 SUM() Returns the sum of the given values. 6 VAR() Returns variance of the given values.

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.

Did you know we work 24x7 to provide you best tutorials