1. Introduction to Grouping & Sorting
In this article, we will explore group and sort the result of an SQL query. The Order By informs SQL engine to arrange the query output in ascending or descending order. To put it differently, the Order By will sort the output query result on a specific column or columns. We will also see how the TOP option behave with the Order By clause and then learn the need for ‘Top With Ties’. After learning Order By, we will move on to the Group By clause to group the items under specific column or columns. Once items are grouped we can apply the Aggregate Functions like sum, max etc on that group also filter the Group through Having Clause. In the example, we will see all these with examples. Here we used Microsoft supplied NorthWnd database. You can use any database of your choice.
2. Example 01 – ‘Order By’ Clause
In the below example, we sorted our SQL Query result in Ascending Order. This is done by using the Order By Clause on the ContactTitle column. The default sorting order in SQL Server is ascending order. This below picture shows the first example on sorting:
3. Example 02 – ‘Order By’ Descending
The above query sorted the result in the Ascending order. Now look at the below example which sorts the same contact title column in descending order. We should specify the DESC keyword in the Order By Clause to sort it in descending order.
4. Example 03 – ‘Order By’ With More Than One Column
Now we will Look at our next below example:
The above query sorts the result based on two columns defined in the Order By Clause. First, SQL Server arranged the contact title in the ascending order and within that it sorted the result in a descending based on Customer Id. In the above picture these two sorting are marked with Red Arrow.
5. Example 04 – Order by with Top
Now we will look at the below example. It shows the usage of the Top Clause with the Order By clause.
In the above Query, we asked for top 10 records. SQL Server first performs sorting and then picks top 10 records from it. The highlighted rows are not the complete records for the country Brazil. The next query shows how we can bring complete records for the column specified in the Order by clause.
6. Example 05 – ‘Top With Ties’ and Order By
As discussed in the previous example, to bring the complete records for the country which take part in the top 10, use the top 10 ‘ with ties ‘ option. Below is the query output:
7. Example 06 – Aggregate Functions on Table Records
The SQL Server Aggregate Functions will work on groups records and will compute result like Sum, Average etc,. If we do not specify grouping, SQL Server applies it to entire records in the table. The below example shows the usage of the count() function that tells how many records exist in the table in which we call it.
In the above query, count(*) applies to all columns and because of this if a record has a single column with not null value then the function counts it as 1. The query result says product table has 77 valid records. If we want to count specific columns, then we can send that to the count function like count(column-name). Here, the aggregate counts column name which does not have the null value. The below table shows the other aggregate functions in the below table:
| Aggregate Name | Usage |
|---|---|
| Sum | Calculates summation of the specified column. |
| Avg | Calculates Average of the specified column. |
| Min | Finds Minimum value in the specified Column |
| Max | Finds Maximum value in the specified Column |
8. Example 07 – Aggregate Function With Where Clause
The below example shows the usage of the count aggregate function on a query that used a ‘ where’ clause in it. First, SQL Server applies the records filter, then it invokes the count aggregate function on the filtered result. Note, the aggregate function skips the null columns while it do counting.
9. Example 08 – One more Example for Aggregate
The example below is same as the previous one except that we used a different aggregate function called sum. The query is to get the Unit price total of the product that falls under the category 2.
10. Example 09 – Aggregate With ‘Group By’ Clause
All the aggregate function example shown so for is applied to all the records returned by the query. Simply, it considers whole table as a group and applied the aggregate function on it. One can use Group By clause to get sub-group of the whole record set returned by a query. Then SQL Server will apply the Aggregate on each sub-groups on the Table.
In the below query, SQL Server groups all the records based on category ID. So, if there are three categories then there will be three subgroups. Here, the SQL Server Applied the count aggregate function on each group. So, the result shows us the number of product available in each category.
11. Example 10 – Aggregate With ‘Group By’ Clause and ‘Having’ Clause
Have a look at the result of the above query. Now, we will filter the product category, which has product count of over 10. As we know the count is an aggregate, and it is applied on the set of records. Because of this fact, we cannot use a Where Clause to apply the filter on count aggregate. We should perform the filter on the group level and not at the record level. The ‘ Having Clause’ of SQL Server works similar to the Where Clause. Simply, ‘ Where Clause’ is for filtering the records and ‘ Having Clause’ is to filter the sub-groups returned. The below query will return all the categories which has more than 10 products and it uses the Having Clause on the Groups.
12. Example 11 – Let us Put It Together
Have a look at the below query and the result. There is nothing special in it. But, it has all you learnt in this article which kept together in it.
Below is the short explanation on how SQL server sees the above query and retrieves the data from the database:
- First, a column filter will be applied and SQL Server will take only two columns CategoryID and ProductId.
- Then SQL Server will Apply the Record filter on the records using the condition specified in the where clause.
- Now, SQL Server will form the filtered in sub-groups. This sub-group is based on CategoryId.
- After the group, a Not NULL count on ProductID on each sub-group based on CategoryId is calculated [Count(CategoryID)]
- The above-formed sub-group is again filtered based on the condition given in the Having clause.
- Finally, in the end result, SQL Server will perform the sorting. Here, the order by clause will do the sorting based on the sub-group count of valid productId.