SAP HANA: ORDER BY, GROUP BY and HAVING Clause

 SAP HANA: ORDER BY, GROUP BY and HAVING Clause:

SQL ORDER BY Statement

The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. SAP HANA sorts query results in ascending order by default.
Order by should appear at the end of your select statement after your WHEREGROUP BY and HAVING clauses if any or all of them exist.

Syntax for using SQL ORDER BY clause to sort data is:


SELECT column-list
FROM table_name [WHERE condition]
[ORDER BY column1 [, column2, .. columnN] [DESC or ASC]];

Example: Employee Table
ID
NAME
DEPT
AGE
SALARY
LOCATION
100
Kabil
Electrical
24
25000
Coimbatore
101
Nazeer
Electronics
28
35000
Coimbatore
102
Aravind
Aeronautics
28
35000
Chennai
103
Kalai
Electronics
22
20000
Coimbatore
104
Keerthi
InfoTech
25
30000
Bangalore

For Example:

If you want to sort the employee table by the salary of the employee, the SQL query would be.

CODE 1:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY "SALARY";
Result:
NAME
SALARY
Kalai
20000
Kabil
25000
Keerthi
30000
Nazeer
35000
Aravind
35000

CODE 2:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY 1;
Then the above code “1” indicates the 1st Column which we used in our select statement. Here I used “NAME” as a 1st column. So, the results set order by Name.
Result:
NAME
SALARY
Aravind
35000
Kabil
25000
Kalai
20000
Keerthi
30000
Nazeer
35000

CODE 3:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY "NAME","SALARY";
The query first sorts the result according to name and then displays it. You can also use more than one column in the ORDER BY clause. If you want to sort the employee table by the name and salary.
Result:
NAME
SALARY
Aravind
35000
Kabil
25000
Kalai
20000
Keerthi
30000
Nazeer
35000

To Sort Data in Descending Order

By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it as shown below.

CODE 4:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY "NAME","SALARY" desc;
The above query sorts only the column 'salary' in descending order and the column 'name' by ascending order.
Result:
NAME
SALARY
Aravind
35000
Kabil
25000
Kalai
20000
Keerthi
30000
Nazeer
35000
If you want to select both name and salary in descending order, the query would be as given below.

CODE 5:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY "NAME" desc,"SALARY" desc;
Result:
NAME
SALARY
Nazeer
35000
Keerthi
30000
Kalai
20000
Kabil
25000
Aravind
35000

How to use expressions in the ORDER BY Clause?

Expressions in the ORDER BY clause of a SELECT statement.

For example:

If you want to display employee name, current salary, and a 20% increase in the salary for only those employees for whom the percentage increase in salary is greater than 30000 and in descending order of the increased price, the SELECT statement can be written as shown below

CODE 6:

Select "NAME","SALARY","SALARY" * 1.2 as NEW_SALARY FROM "KABIL_PRACTICE"."DEMO_EMP" Where "SALARY" * 1.2 > 30000 ORDER BY NEW_SALARY desc;
Result:
NAME
SALARY
NEW_SALARY
Nazeer
35000
42000
Aravind
35000
42000
Keerthi
30000
36000

SQL GROUP BY Clause

The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.
The GROUP BY clause is a SQL command that is used to group rows that have the same values.
The queries that contain the GROUP BY clause are called grouped queries and only return a single row for every grouped item.

For Example:

If you want to know the total amount of salary spent on each department, the query would be:

CODE 1:

Select "DEPT"SUM("SALARY"from "KABIL_PRACTICE"."DEMO_EMP" Group by "DEPT";
Result:
DEPT
SUM(SALARY)
Electrical
25000
Electronics
55000
Aeronautics
35000
InfoTech
30000

NOTE:
The group by clause should contain all the columns in the select list expect those used along with the group functions.

CODE 2:

Select "LOCATION","DEPT"SUM("SALARY"from "KABIL_PRACTICE"."DEMO_EMP" Group by "LOCATION","DEPT";

Result:
LOCATION
DEPT
SUM(SALARY)
Coimbatore
Electrical
25000
Coimbatore
Electronics
55000
Chennai
Aeronautics
35000
Bangalore
InfoTech
30000

SQL GROUP BY with HAVING Clause

Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

For Example:

If you want to select the department that has total salary paid for its employees more than 30000, the SQL query would be like;

CODE 1:

Select "DEPT",SUM("SALARY"from "KABIL_PRACTICE"."DEMO_EMP" Group by "DEPT" Having SUM("SALARY") > 30000 ;
Result:
DEPT
SUM(SALARY)
Electronics
55000
Aeronautics
35000

Note:
We can use Order by at the end of the above code:

CODE 2:

Select "DEPT"SUM("SALARY"from "KABIL_PRACTICE"."DEMO_EMP" Group by "DEPT" Having SUM("SALARY") > 30000 order by "DEPT";

Result:
DEPT
SUM(SALARY)
Aeronautics
35000
Electronics
55000
Note:
When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement, the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped based on the GROUP BY clause.
Finally, any conditions on the group functions in the HAVING clause are applied to the grouped rows before the final output is displayed.



Thank you for visiting my Blog...
Share your Comments...

0 Comments

Thanks for visiting our site : sapguidanceconsultant.blogspot.com
For Fast Response message us through "Contact Form".

Post a Comment

Thanks for visiting our site : sapguidanceconsultant.blogspot.com
For Fast Response message us through "Contact Form".

Post a Comment (0)

Previous Post Next Post