Use Group Functions in ORDER BY Clause in Oracle

Q

Can Group Functions Be Used in the ORDER BY Clause in Oracle?

✍: FYIcenter.com

A

If the query output is aggregated as groups, you can sort the groups by using group functions in the ORDER BY clause. The following statement returns how many employees are having the same salary in each department. The group output is sorted by the count in each group in descending order:

SQL> SELECT department_id, salary, count(*) 
  2  FROM employees GROUP BY department_id, 
  3  salary HAVING count(*) > 1 
  ORDER BY COUNT(*) DESC;
DEPARTMENT_ID     SALARY   COUNT(*)
------------- ---------- ----------
           50       2500          5
           50       3200          4
           50       2800          3
           80      10000          3
           80       9500          3
           50       3100          3
           50       2600          3
.....

⇒Understanding SQL SELECT Query Statements in Oracle

⇒⇒Oracle Database Tutorials

2019-10-27, 886👍, 0💬

Use Multiple Columns in GROUP BY in Oracle
Can Multiple Columns Be Used in GROUP BY in Oracle? You can use multiple columns in the GROUP BY clause as shown in the following example. It returns how many employees are having the same salary in each department: SQL> SELECT department_id, salary, count(*) 2 FROM employees GROUP BY departm... 2019-10-27, 915👍, 0💬

Divide Query Output into Groups in Oracle
How To Divide Query Output into Groups in Oracle? You can divide query output into multiple groups with the GROUP BY clause. It allows you specify a column as the grouping criteria, so that rows with the same value in the column will be considered as a single group. When the GROUP BY clause is speci... 2019-11-21, 899👍, 0💬

Use Group Functions in ORDER BY Clause in Oracle
Can Group Functions Be Used in the ORDER BY Clause in Oracle? If the query output is aggregated as groups, you can sort the groups by using group functions in the ORDER BY clause. The following statement returns how many employees are having the same salary in each department. The group output is so... 2019-10-27, 887👍, 0💬

Query with a Left Outer Join in Oracle
How To Write a Query with a Left Outer Join in Oracle? If you want to query from two tables with a left outer join, you can use the LEFT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a left outer join from two tables: departments and employees. The join conditi... 2019-10-18, 880👍, 0💬

Group Functions Used with Non-group Selection Fields in Oracle
Can Group Functions Be Mixed with Non-group Selection Fields in Oracle? If a group function is used in the SELECT clause, all other selection fields must be group level fields. Non-group fields can not be mixed with group fields in the SELECT clause. The script below gives you an example of invalid ... 2019-11-21, 861👍, 0💬

Define and Use Table Alias Names in Oracle
How To Define and Use Table Alias Names in Oracle? When column names need to be prefixed with table names, you can define table alias name and use them to prefix column names as shown in the following select statement: SQL> SELECT e.first_name, e.last_name, d.department_name FROM employees e ... 2019-10-27, 807👍, 0💬

Query with a Right Outer Join in Oracle
How To Write a Query with a Right Outer Join in Oracle? If you want to query from two tables with a right outer join, you can use the RIGHT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a right outer join from two tables: departments and employees. The join con... 2019-10-18, 807👍, 0💬

Count Duplicated Values in a Column in Oracle
How To Count Duplicated Values in a Column in Oracle? If you have a column with duplicated values, and you want to know what are those duplicated values are and how many duplicates are there for each of those values, you can use the GROUP BY ... HAVING clause as shown in the following example. It re... 2019-11-21, 775👍, 0💬

Apply Filtering Criteria at Group Level in Oracle
How To Apply Filtering Criteria at Group Level in Oracle? If you want to return only specific groups from the query, you can apply filtering criteria at the group level by using the HAVING clause inside the GROUP BY clause. The following script gives you a good HAVING example: SQL> SELECT dep... 2019-11-21, 730👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.