SQL Server Aggregate Functions | SQL Beginner Guide

SQL Server offers several built-in functions. 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

  1. COUNT ()                  returns the total number of values in a given column
  2. SUM ()                       returns the sum of the numeric values in a given column
  3. AVG  ()                       returns the average value of a given column
  4. MIN()                         returns the smallest value in a given column
  5. 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.

SQL Server Aggregate Functions

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.

The SQL COUNT(), AVG() and SUM() Functions

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 table

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];
 
Count function in sql

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 :

Count where

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

sql server average function

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]
sql sum

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
sql sum count

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

sum count difference

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

sql sum vs count

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]
SQL min()

This returns minimum marks obtained by a student

MAX() Example

SELECT MAX(Grade) As TotalGrades
FROM [StudentGrade]
SQL max()


This returns maximum marks obtained.

SQL Aggregate functions 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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s