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

For this we will use StudentGrade table. See it’s data belowsql server average function

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(Grade) 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

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]

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

Author: Awais

Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows ! Happy Reading and Learning!

Leave a Reply