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
- 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.
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 COUNT Syntax
SELECT COUNT (columnname)
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;
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
SELECT AVG (columnname) FROM tablename
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
SELECT SUM (columnname) FROM tablename
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.
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
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
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
SELECT MIN(columnname) FROM tablename
SELECT MAX(columnname) FROM tablename
SELECT MIN(Grade) As TotalGrades FROM [StudentGrade]
This returns minimum marks obtained by a student
SELECT MAX(Grade) As TotalGrades FROM [StudentGrade]
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