Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

Learn How to Use Order By Clause and Group By Clause

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:

SQL 'Order By' Example
SQL ‘Order By’ Example

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.

SQL 'Order By' clause with Descending order Example
SQL ‘Order By’ clause with Descending order Example

4. Example 03 – ‘Order By’ With More Than One Column

Now we will Look at our next below example:

SQL Order By clause with More than one Column
SQL Order By clause with More than one Column

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.

SQL Order By with Top 10
SQL Order By with Top 10

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:

SQL Order By And Top With Ties
SQL Order By And Top With Ties

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.

SQL Aggregate Function Example
SQL Aggregate Function Example

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 NameUsage
SumCalculates summation of the
specified column.
AvgCalculates Average of the
specified column.
MinFinds Minimum value in the specified
Column
MaxFinds Maximum value in the specified
Column
Aggregate Function & its usage

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.

SQL Aggregate with where clause
SQL Aggregate with where clause

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.

SQL Aggregate One More Example
SQL Aggregate One More Example

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.

SQL Aggregate With Group By Clause
SQL Aggregate With Group By Clause

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.

SQL Aggregate With 'Group By' and 'Having'
SQL Aggregate With ‘Group By’ and ‘Having’

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.

SQL Query with Aggregate, Group By, Order By and Having with Where conditions
SQL Query with Aggregate, Group By, Order By and Having with Where conditions

Below is the short explanation on how SQL server sees the above query and retrieves the data from the database:

  1. First, a column filter will be applied and SQL Server will take only two columns CategoryID and ProductId.
  2. Then SQL Server will Apply the Record filter on the records using the condition specified in the where clause.
  3. Now, SQL Server will form the filtered in sub-groups. This sub-group is based on CategoryId.
  4. After the group, a Not NULL count on ProductID on each sub-group based on CategoryId is calculated [Count(CategoryID)]
  5. The above-formed sub-group is again filtered based on the condition given in the Having clause.
  6. 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.

Categories: SQL Server

Tags: , , ,

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.