Core SAP HANA SQL script concepts- GROUP BY, IN & BETWEEN,ORDER BY

Welcome to the fourth installment of this SAP HANA SQL Scripts core concepts section where we try to understand how to use SQL GROUP BY to aggregate rows of data, learn IN and BETWEEN clauses for applying filter with multiple values and also how to sort data using ORDER BY.

Let’s start aggregating…SAP HANA SQL Script GROUP BY

We now take up a new requirement – one to see the salary by country. This information is present in table EMP_SALARY. So we pick up this information using the SELECT statement as always.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

We see the below output with 50 rows of information.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

But what we if we need to have the countries displayed with a sum of their total salaries paid. In this case, we need to use the SUM aggregate function. And whenever you use aggregate functions, any field that is not in an aggregate function must be in a GROUP BY clause after the table name.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

The execution result is as below. We now see the sum of all salaries of each country. I have not used an alias for this summation field but feel free to do so.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

GROUP BY with HAVING Clause

When you have a GROUP BY in your statement, you have an option to use the HAVING clause also. It is similar to a WHERE clause but only works with aggregate functions. So if you want to display the countries and their collective aggregations of salary paid but you only want to display the countries which paid less than 40,000 in total. In this case, the total isn’t available in the table but also needs to be calculated on run time. Hence we use the HAVING clause with the same aggregation as shown below.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

Execution produces the below results which are clearly filtered out by the HAVING clause as per the requirement.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

Now what if you needed to add a filter too, the WHERE condition should be ahead of the GROUP BY. In this example, I add the filter to pick only Country codes IN and FR. As always, field names are in double quotes, character values in single quotes and integer values without quotes.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

Executing this provides the below result.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

IN Operator

Note that you could have also written this filter as “COUNTRY” IN (‘IN’,’FR’) which on execution would produce the same result.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

As expected, the result is:

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

BETWEEN Operator

Sometimes we would also need to filter a range of values. For this use the BETWEEN..AND keyword combination. To filter employee IDs between 1001 and 1010, use this keyword combination as shown below. Note again that the field in filter doesn’t need to be in the list of fields that are selected for output.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

On execution, we get the below result.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

Order by in SQL Statement



To understand order by, let’s switch to the EMP_NAMES table for a short while.
It contains the Employee ID, first name and last name. There comes a need at times to sort one or more columns of data. We use the Order by clause for this operation. Order by should appear at the end of your select statement after your WHERE, GROUP BY and HAVING clauses if any or all of them exist.
Let’s now sort the EMP_NAMES table by First Name in ascending sequence. The default sort of order by is ascending and hence in this case you don’t have to write a keyword for this specifically. Hence we write the below statement.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

The execution result proves that the result set got sorted by first name of the employees

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

Now, to make the ORDER BY do a descending sort, all you have to do is add the desc keyword at the end as shown below.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

As shown below, the sort works as expected.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE



As explained before, the sequence of keywords is

SELECT <fields> from <table> WHERE <conditions> GROUP BY<every_unaggeggated_field> HAVING<aggregation_filter> ORDER BY <sort_sequence>

Not all of these clauses are mandatory. It varies as per what you need to fulfil your requirement.

Now, coming back to the EMP_SALARY table, we now demonstrate a simple example where all of these clauses are used.

Here we need the sum of salaries by country for only those employees who’s ID is between 1005 and 1030. When the sum is done, the display should show only those countries who’s salary summation is less than 50000. Also, the overall list must be sorted descending by country ID. The below code reflects these statements.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

The result set is as shown below.

SAP HANA SQL SCRIPT GROUP BY IN BETWEEN ORDER BY CLAUSE

I hope this was easy to understand and follow. Read the next “Part-5” tutorial to understand how you can use string functions to manipulate the character data sets.

Make sure to share this content on social media using the share buttons below to show your support to this website and to keep it alive.

<<Previous Tutorial                                                                                                              Next Tutorial>>

Content Protection by DMCA.com
Tagged , , , , , , , , , , , , , , , , , .

6 Comments

  1. Each and every tutorial of yours is simply awesome.. I am going through each and every page and clearing my doubts.
    Thanks a lot.. short of words to describe these tutorial’s impact on my knowledge!!!

Leave a Reply