** Aggregate Functions In SQL Server** built-in functions that SQL provides to do the aggregation operations . SQL Aggregate functions are among the ones most widely used in reporting & data joining scenarios. Following are few SQL Aggregate functions we will discuss today

## Sql Server Aggregate Functions

- COUNT () returns the total number of values in a given column
- SUM () returns the sum of the numeric values in a given column
- AVG () returns the average value of a given column
- MIN() returns the smallest value in a given column
- MAX () returns the largest value in a given column

## What is Aggregate

Anytime when I start learning about anything , my best practice is to learn meaning of the word used. So , let’s start with * aggregate* and it’s meaning.

If you search google it says

A whole formed by combining several separate elements

This couldn’t be more precise than this. In SQL Aggregate is when we combine a column to give out the single value result. For instance , you may be interested in average grades of a student. You may want to see the total number of courses taken up by a Student. Maximum marks obtained , minimum marks obtained & many such scenarios can come up where you want to see aggregated form rather than a long 100 rows of data. For such purposes SQL Server has Aggregate Functions.

## Aggregate Functions In SQL with Examples

If you’re looking to work in database , data warehouse or Business Intelligence you have to have a very strong grip on this topic. Reason is simple , databases are built so that they can be used later to retrieve data for reports. People who make decisions only see the aggregated data. Then if necessary , they go for detailed data.

I will use our sample database for the demonstration of these functions. I recommend you to copy paste the code in your SQL Server Management Studio and just execute the code. You will have a sample database to practice it on.

## SQL COUNT( ) Function

It is use for counting the number of rows for any columns. It only counts them regardless of what type of data they have. Let’s use Course table for this function. In case you don’t have the sample database. Look at the table below.

## SQL COUNT Syntax

```
SELECT COUNT (columnname)
FROM tablename
```

## COUNT() Examples

Just put column name and you will have the count of rows for that columns. Using our sample database let’s see total courses we have .

```
SELECT COUNT (Title) AS TotalCourses
FROM [Course];
```

Our column Title has 10 rows according to our query which is correct.

What if you want to see how many courses have 4 credit hours . Simply introduce *Where clause*

```
SELECT COUNT (Title) AS CoursesWithCreditH
FROM [Course]
WHERE Credits =4;
```

Result :

You can find total number of rows in a table by using this function as well.

```
SELECT COUNT (*) As NumberOfRows
FROM [Course]
```

Result will be 10.

## SQL Server Average Function AVG()

This function returns the average value of a numeric column. Simply returns the average

#### SYNTAX

`SELECT AVG (columnname) FROM tablename `

## AVG() Examples

For this we will use StudentGrade table. See it’s data below

If you want to see the average grades of students for all subjects. Run this query

```
SELECT AVG([Grade]) As AverageGrades
FROM [StudentGrade]
```

Can you introduce a WHERE clause ? Comment below if you can!

### SUM() Function In SQL

This SQL aggregate function sums up all the values in the column & returns a single value

#### SYNTAX

`SELECT SUM (columnname) FROM tablename `

## SUM() Example

Write a query that returns total grades obtained by the whole class.

```
SELECT SUM([Grade]) As TotalGrades
FROM [StudentGrade]
```

Introduce a StudentID in where clause so that we can see the total marks by a student

```
SELECT SUM([Grade]) As TotalGrades
FROM [StudentGrade]
WHERE StudentID=2
```

## Difference Between SUM() & Count ()

Database developers often mix Sum with Count. These two functions have very clear names & their purpose is also exactly as written. The mistake is committed in it’s execution. Sometimes , we want the total number of rows in SQL table. You should use count function for that & when you want to sum the values in it then Use Sum Function.

See below

```
SELECT COUNT(*) As TotalRows
FROM [StudentGrade]
```

This will give you total number of rows which is **40, **reason is simple because now you are counting the number of rows in table.

Now execute this query

```
SELECT COUNT(*) As TotalRows
FROM [StudentGrade]
```

Result will be 36 rows. You know why ? Because our Column Grade has 4 NULLS in it.

Now consider this . What if you want to see the total marks obtained by the StudentID 2. Will you use Count or SUM?

Best way is to run queries on it. When we run this query

```
SELECT COUNT(Grade) As TotalRows
FROM [StudentGrade]
WHERE StudentID=2
```

It returns

Does this answer my question. I wanted to see the total marks not the total rows.

Let’s Run SUM query

```
SELECT SUM(Grade) As TotalGrades
FROM [StudentGrade]
WHERE StudentID=2
```

It Returns

This is actual answer.

So keep in mind the difference between Total Number of Rows is Count & Total of a column in SUM

## MIN () & MAX () FUNCTION

Min() Stands for minimum & Max () Stands for Maximum. They are used to find minimum/maximum values in a column

#### SYNTAX MIN()

`SELECT MIN(columnname) FROM tablename `

#### SYNTAX MAX()

`SELECT MAX(columnname) FROM tablename `

## MIN() Example

```
SELECT MIN(Grade) As TotalGrades
FROM [StudentGrade]
```

This returns minimum marks obtained by a student

## MAX() Example

```
SELECT MAX(Grade) As TotalGrades
FROM [StudentGrade]
```

This returns maximum marks obtained.

Aggregate Functions In SQL are super useful in summary reports , in ETL , in data auditing & plenty more. You may have noticed that in all Examples I all only used one column. That is not co-incidence, If you introduce a second column in SELECT query you have to use GROUP BY clause & HAVING Clause for slicing the data. Group By & Having is our next article. We will learn how to use SQL aggregate functions to find useful data & a report.

Hope you have enjoyed this article & please do comments if you think there is a better explanation needed. See you next time

Happy Learning!

Cheers!