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.
We see the below output with 50 rows of information.
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.
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.
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.
Execution produces the below results which are clearly filtered out by the HAVING clause as per the requirement.
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.
Executing this provides the below result.
IN Operator
Note that you could have also written this filter as “COUNTRY” IN (‘IN’,’FR’) which on execution would produce the same result.
As expected, the result is:
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.
On execution, we get the below result.
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.
The execution result proves that the result set got sorted by first name of the employees
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.
As shown below, the sort works as expected.
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.
The result set is as shown below.
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.