Oracle DB to group rows-group by, having__oracle

Create a data groupAll group functions treat a table as a large group of information. However, it is sometimes necessary to divide this information table into smaller groups. You can accomplish this task by using the GROUP BY clause.
Create a Data group: syntax for a GROUP BY clauseYou can divide rows in a table into smaller groups by using the GROUP BY clause. SELECT column, group_function (column) from table [WHERE condition] [GROUP BY group_by_expression] [order by column];

You can use the GROUP BY clause to divide rows in a table into groups. You can then use Group functions to return summary information for each group. In this syntax: group_by_expression specifies some columns, the values of which determine the baseline criteria for grouping rows · Unless you specify a single column in the GROUP BY clause, you cannot select a single result, even if you include a group function in the SELECT clause. If you do not include a list of columns in the GROUP BY clause, you receive an error message. • By using a WHERE clause, you can exclude rows before dividing them into groups. • Columns must be included in the GROUP BY clause. • Column aliases cannot be used in the GROUP BY clause.
using the GROUP BY clauseAll columns in the select list that do not appear in the group function must be included in the GROUP BY clause. Hr@test0924> SELECT department_id, AVG (salary) from Employees GROUP by department_id;
department_id AVG (SALARY)------------------------100 8601.33333 30 4150            7000 20 9500 70 10000 90 19333.3333 110 10154 50 3475.55556 40 6500 80 8955.88235 10 4400 60 5760
Rows selected.
When using the GROUP BY clause, make sure that all columns that are not present in the select list in the group function are included in the GROUP BY clause. The example shows the department number and average salary for each department. The following describes how a SELECT statement with a GROUP by clause is evaluated: The SELECT: clause specifies the column to retrieve, as follows:-EMPLOYEES: The Department number column in the table-the average value of all salaries in the group specified by the groups by: clause from: clause specified number According to the table the library must access: Employees table. WHERE: clause specifies the row to retrieve. Because there is no WHERE clause, all rows are retrieved by default. GROUP BY: clause Specifies how rows are grouped. Because rows are grouped by department number, the AVG function applied to the Payroll column calculates the average salary per department. Note: To sort query results in ascending or descending order, include an ORDER BY clause in your query.
using the GROUP BY clauseThe GROUP by column does not necessarily appear in the select list. Hr@test0924> SELECT AVG (Salary) from Employees GROUP by department_id;
AVG (SALARY)-----------8601.33333 4150 7000 9500 10000 19333.3333 10154 3475.55556 6500 8955.88235 4400 5760
Rows selected.

The GROUP by column does not necessarily appear in the SELECT clause. For example, the SELECT statement in the example shows the average salary for each department, but does not display the corresponding department number. But without the department number, the result seems pointless. You can also use a group function in the ORDER BY clause: hr@test0924> SELECT department_id, AVG (salary) from Employees group by department_id ORDER by AVG (salary);
department_id AVG (SALARY)------------------------50 3475.55556 30 4150 10            4400 60 5760 40 6500 7000 100 8601.33333 80 8955.88235 20 9500 70 10000 110 10154 90 19333.3333
Rows selected.
GROUP by multiple columnsSometimes, you need to see the results of each group within the group. Hr@test0924> SELECT department_id, job_id, sum (salary) from Employees GROUP by department_id, and job_id order by job_id;
department_id job_id SUM (SALARY)----------------------------------Ac_account 8300 1 Ac_mgr 12008 Ad_asst 4400 ... Rows selected.

This example displays a report that shows the sum of the salaries to be paid for each position in each department. The Employees table is grouped first by department number, and then by title in each group. For example, divide four warehouse employees in department 50 into one group and generate a result for all warehouse staff in that group (the sum of salaries).



using the GROUP BY clause for multiple columnsHr@test0924> SELECT department_id, job_id, SUM (salary) from Employees WHERE department_id > GROUP by department_i D, job_id order by department_id;
department_id job_id     SUM (SALARY)----------------------------------          &N Bsp;50 Sh_clerk         64300            50 St_clerk     &NBSP ;   55700            50 St_man           36400     &NB Sp      60 It_prog          28800            70 Pr_rep           10000            80 Sa_man         &N Bsp 61000            80 sa_rep          243500       &NB Sp    90 ad_pres          24000            90 AD_VP   & nbsp        34000           fi_account       39600   &NBSP ; &nbsp     fi_mgr           12008           Ac_account        8300           ac_mgr           12008
Rows selected. You can return summary results for groups and subgroups by listing multiple group by columns. The GROUP BY clause groups rows, but does not guarantee the order of the result set. To sort the groups, use the ORDER BY clause. In the example, the SELECT statement that contains the GROUP by clause is evaluated as follows: The SELECT clause specifies the column to retrieve:-Employees The Department in the table id-employees the job in the table id-group the sum of all the salaries in the group specified by the By clause · The FROM clause specifies the table that the database must access: the Employees table. The WHERE clause limits the result set to rows with a department ID greater than 40. The GROUP BY clause specifies how the result rows should be grouped:-First, the rows are grouped by Department ID-second, the rows are grouped by job ID in the departmental ID group ORDER by the Department ID to sort the results. Note: The SUM function will be applied to the payroll for all job IDs in the result set of each department ID group. Also, note that you do not return sa_rep rows. The Department ID for this row is null, so the where condition is not met.
illegal queries using group functionsAny column or expression in the SELECT list that is not in a clustered function must appear in the GROUP BY clause: hr@test0924> SELECT department_id, COUNT (last_name) from employees; SELECT department_id, COUNT (last_name) from Employees * ERROR in line 1:ora-00937:not a single-group group Functi On must add a GROUP BY clause in order to count each department_id's corresponding last name.
Hr@test0924> SELECT department_id, job_id, COUNT (last_name) from Employees GROUP by department_id; SELECT department_id, job_id, COUNT (last_name) from Employees GROUP by department_id * ERROR in line 1:ora-00979:not a GROUP by expression
Either add job_id to group by or remove job_id columns from the select list.


As long as a single item (DEPARTMENT_ID) and a group function (COUNT) are mixed in the same SELECT statement, you must include a GROUP BY clause that specifies the individual items (department_id in this case). If the GROUP BY clause is missing, the error message "Not a Single-group the group function (not a group of groups)" is displayed, and an asterisk (*) that points to the error column appears. You can correct the error in the first example by adding a GROUP by clause: hr@test0924> SELECT department_id, Count (last_name) from Employees GROUP by department_id ;
department_id COUNT (last_name)-----------------------------                         6            30                6                              1            20                2            70                1            9 0                3                 &NB Sp        2            50                          40                1     &NBS P     &NBsp;80                          10                1            60                5
Rows selected.
Any column or expression in the select list that is not in a clustered function must appear in the GROUP BY clause.
In the second example, job_id is neither in the GROUP BY clause nor in the SET function, so the "not a group byexpression (not the group BY expression)" error appears. You can correct the error in the second example by adding job_id in the GROUP BY clause. Hr@test0924> SELECT department_id, job_id, COUNT (last_name) from Employees GROUP by department_id, job_id;
department_id job_id COUNT (last_name)---------------------------------------Ac_account 1 AD_VP 2 St_clerk 20 ... Rows selected.

illegal queries using group functions • You cannot qualify a group with a WHERE clause. Hr@test0924> SELECT department_id, avg (Salary) from Employees WHERE avg (Salary) > 8000 GROUP by department_id;                                                         SELECT department_id, AVG (Salary) from Employees WHERE AVG (Salary) > 8000 GROUP by department_id * ERROR at line 1:ora-00934:group function isn't allowed here

• Groups can be qualified using the HAVING clause. • You cannot use group functions in the WHERE clause.
You cannot qualify a group with a WHERE clause. The SELECT statement in the example produces an error because the statement uses a WHERE clause to qualify the average salary for those departments whose average salary is greater than $8,000. However, by using the HAVING clause to qualify a group, you can correct the error in the example: Hr@test0924> SELECT department_id, AVG (salary) from Employees GROUP by department_id Having AVG (salary) > 8000;
department_id AVG (SALARY)------------------------100 8601.33333 20 9500 70 10000 90 19333.3333 110 10154 80 8955.88235
6 rows selected.
Qualifying Group ResultsYou can use the HAVING clause to qualify a group in the same way that you qualify the selected row with the WHERE clause. To find the highest salary in each department with a maximum salary greater than $10,000, you need to do the following: 1. Find the highest salary in each department by sector number. 2. Limit the group to departments with a maximum salary greater than $10,000.
qualifying Group results with HAVING clauseWhen you use the HAVING clause, Oracle Server qualifies the group in the following ways: 1. Group the rows. 2. Apply group functions. 3. Display groups that conform to the HAVING clause. SELECT column, group_function from table [WHERE condition] [GROUP by group_by_expression] [have group_condition] [O Rder by column];
You can use the HAVING clause to specify the group to display, which further qualifies the group based on the summary information. In the preceding syntax, group_condition is used to qualify the return row group of the group that satisfies the specified criteria. When you use the HAVING clause, Oracle Server performs the following steps: 1. Group rows. 2. Apply group functions to groups. 3. Displays groups that conform to the criteria in the HAVING clause. A HAVING clause can be placed before the GROUP BY clause, but it is recommended that the GROUP BY clause precede it because it is more logical. You should first form a group and compute the group functions, and then apply the HAVING clause to the groups in the select list. Note: the WHERE clause qualifies the row, and the HAVING clause qualifies the group.
using the HAVING clauseHr@test0924> SELECT department_id, Max (salary) from Employees GROUP by DEPARTMENT_ID has Max (salary) >10000;
department_id MAX (SALARY)------------------------100 12008 30 11000 20 13000 90 24000 110 12008 80 14000
6 rows selected.
Examples show departmental numbers and maximum salaries for departments with a maximum salary greater than $10,000. You can use the GROUP BY clause in the select list without using a group function. If you qualify rows based on the results of a group function, you must take the GROUP BY clause and the HAVING clause. The following example shows the department number and average salary for departments with a maximum salary greater than $10,000: <