Aggregate functions are in built functions that sql server uses to perform some basic tasks. If you need a more complex function to perform a task, then you have to write a user defined function for your operations. l’ll be talking about functions later in the tutorial. Ok, let’s take a look at the Aggregate functions supported by the SQL server.
We have SUM, MIN, MAX, COUNT, AVG. The names mean exactly what they do. So if you want to sum some columns together, you use sum, if you want to find the minimum value in a column, you use the min, if you want to find the maximum value in a particular column, you use the min function, to get the average of values in a column, you use the AVG function. The count is use to count the number of values either in a column or the number of records in the entire table.
One important statement that goes with the aggregate functions is the group by clause adn the having clause. The group by is used to indicate the column name on which the aggregation is going to be done on. There are rules that must be followed when using group by clause.
Let’s take the aggregate functions and see how to implement them. Let’s say we want to find the number of records in a table called person. we can do it like this,
SELECT COUNT(*)
FROM person
Now, if you have males and females in the table and you want to find the number of females and the number of males in the person table, that’s where you use the group by clause to group the females together and the males separately together and get counts of each group. So let’s see how we can write the code to give us the total count for each gender. I’m making the assumption that there is a column ‘gender’ in the table that is file with either ‘M’ for males and ‘F’ for females.
SELECT gender, COUNT(gender)
FROM person
GROUP BY gender
This will give you each gender type and the total count for each gender type.
Let’s go ahead and use the average function to find the average number of females and average number of males. The average function works as follows
SELECT gender, AVG(gender)
FROM person
GROUP BY gender
This will give you males and their average, then females and the average number for them. Let’s go ahead and assume we have age also in this table. Then we are interested in finding the oldest age and youngest age. The max and min functions can be used to find this ages.
SELECT MIN(age)
FROM person
To find the oldest age, you use the max like this,
SELECT MAX(age)
FROM person
Now let’s look at another important clause, the having clause. This clause is used for filtering result set. For example, if you want to find total number of males or females only. The having clause can be used to filter the result so only one group will show up as the result set.
SELECT COUNT(gender)
FROM person
GROUP BY gender
HAVING gender = ‘M’
We can also use the Where clause instead of the HAVING clause but there are some advantages in using Having rather than Where when the group by clause is used.
1. where filters data on the underlying tables, having is generally used to filter on the result of the group by
2. having can contain aggregate functions, where can not.
Another clause is the order clause, this is used to order the result set in some particular order. Ascending or descending.
order of execution:
first where is applied, then group by is done and then having is applied. Let’s see the general execution plan.
select
from
where
group by
having
order by
–Rules for group by and having clause–
1. If a non-aggregate column is selected along with aggregate function then
all non-aggregate columns must be included in GROUP BY clause
2. All the columns included in the GROUP BY clause may/may not be in select predicate
3. Generally, having is used in conjunction with group by.
4. If l am using Group by clause I may/ may not use Having clause
5. HAVING clause is used to filter data based on aggregate functionality
Hope this helps, Check out for the next tutorial, it’s going to be on System functons.
Keep in touch, Peace.
