Module 2: Group Data with GROUP BY Clause Part 2

Previous Tutorial - Next Tutorial

An aggregate function performs a calculation on a set of values and returns a single value. We have used this function to summarize data. SQL Server provides us various types of built-in aggregate functions such as the first one is the COUNT.

Now COUNT calculate all non-null values for a specific column. It can also be used as a count of star to return the count of rows regardless of null values. So we have already used this count of star in the previous query.

For example, if you specify a column name in the COUNT then it will ignore the null value. But if you specify the count of star(*) then it will consider the null value. The next one is the SUM. Now the SUM function returns the sum of all non-null values for a specified column. Just like COUNT in the SUM you also need to specify column name but you can't specify star in the SUM function. The next one is the AVERAGE.

This AVERAGE function returns the average of all non-null values for a specified column. Internally the query processor calculates the sum of all the values of a specified column and then divides by the number of rows in the range to get the average. The next one is the MIN function which returns the smallest non-null value from a specified column. The last one is the MAX which returns the highest nonnull value from a specified column.

Now let's move on to the SQL server management studio to see all these aggregate functions in action. This time we refer to the records from this product table. So as I said earlier that you can get the total number of records from this table using the COUNT function like (SELECT COUNT(*) FROM Production.Product) and when I execute this query then I will get a total number of records from this table.

But now instead of the count of star what if I specify the column name. In this color column, you can see that some records have a null value, some product records have the same color. Let's say if I specify the color column in the count function(SELECT COUNT (Color) AS TotalRecords FROM Production.Product) and If I execute this query, do you think this time I will get 504 records?

Let's see, so why should I get 256 records instead of 504? Because as I said when you specify the column name then this count aggregate function will ignore the null records. For example, if I execute this query again you can see that the color column has some records as null. This count aggregate function will ignore the records.

Now you can also see that some products have the same color name. If I specify a distinct keyword over here. As we earlier see that the distinct keyword will return the unique record from the results set. Now this time when I execute this query so instead of 256 I will get only 9 records. That means if I specified the distinct keyword over here then it will discard those records that have the same color.

After count let's move to the other function which is SUM. The SUM aggregate function simply returns the sum of numeric column values like the others. This function only considers nonnull values. For example this time we will refer to this sales order detailed table. This time to understand the demo of SUM will refer to this sales order detailed table. If I execute this query for this particular sales order ID you can see that we will get a different unit price.

Now I want the total of this unit price. Let's see how we can use this SUM function to get the total of this unit price. For that, I need to write a query and if I execute the query then I will get a total unit price. This is how you can use the SUM function.

Now let's move to the other function which is the AVERAGE function. The AVERAGE function returns the calculated average for a specified column in case of average internally the query processor calculates the sum of all values of a specified column and then divides the number of rows in the range.

For example, in this query instead of SUM, if I specify AVERAGE, I will get the average unit price or sales order ID data. So the same way you can use the MIN function and MAX function to get the minimum and maximum value of unit price for these sales order IDs.

For example, if I specify the MIN function and if I execute this query then it will give me the minimum unit price value. If I specify the MAX function then it will give me the maximum of the unit price. This is how you can use all these aggregate functions to get the desired result.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials:

• General SQL Introduction
• Module 1: Getting Started with SQL Server
• Module 1: SQL Server Management
• Module 1: Essential SQL Commands
• Module 1: SQL Introduction Essentials
• Module 2: Learning SQL Overview
• Module 2: Logical Query Processing Order
• Module 2: Select Statement Fundamentals
• Module 2: Filtering Data with WHERE Clause Overview
• Module 2: Filter Data with WHERE Clause - Part 1
• Module 2: How to Filter Data with WHERE Clause - Part 2
• Module 2: Filter PDF Data Using WHERE clause - Part 3
• Module 2: Sorting Data Using ORDER BY Clause
• Module 2: Grouping Data with GROUP BY Clause Part 1
• Module 2: Grouping Data with GROUP BY Clause Part 2
• Module 2: Grouping Data with GROUP BY Clause Part 3
• Module 2: Limiting Data With TOP Clause
• Module 2: Summary and Quiz
• Module 3: SQL Joins and SET Operators
• Module 3: Understanding Inner Join
• Module 3: Understanding Outer Join
• Module 3: Cross Join Explained
• Module 3: Self Join Explained
• Module 3: UNION and UNION ALL
• Module 3: INTERSECT and EXCEPT
• Module 3: Understanding Sub-Queries
• SQL Analytic Functions