Using a Column Multiple Times in a GROUP BY Clause

  


SQL> CREATE TABLE employee(
  2    employee_id INTEGER,
  3    division_id CHAR(3),
  4    job_id CHAR(3),
  5    first_name VARCHAR2(10) NOT NULL,
  6    last_name VARCHAR2(10) NOT NULL,
  7    salary NUMBER(6, 0)
  8  );

Table created.

SQL>
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(1, 'BUS','PRE','James','Smith','800000');

1 row created.

SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(2, 'SAL','MGR','Ron','Johnson','350000');

1 row created.

SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(3, 'SAL','WOR','Fred','Hobbs','140000');

1 row created.

SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(4, 'SUP','MGR','Susan','Jones','200000');

1 row created.

SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(5, 'SAL','WOR','Rob','Green','350000');

1 row created.

SQL>
SQL> select * from employee;

EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME      SALARY
----------- --- --- ---------- ---------- ----------
          1 BUS PRE James      Smith          800000
          2 SAL MGR Ron        Johnson        350000
          3 SAL WOR Fred       Hobbs          140000
          4 SUP MGR Susan      Jones          200000
          5 SAL WOR Rob        Green          350000

SQL>
SQL>
SQL> --Using a Column Multiple Times in a GROUP BY Clause
SQL>
SQL> --You can use a column multiple times in a GROUP BY clause, which can enable you to reorganize your data or report on different groupings of data. For example, the following query contains a GROUP BY clause that uses division_id twice, once to group by division_id and the second in a ROLLUP:
SQL>
SQL> SELECT division_id, job_id, SUM(salary)
  2  FROM employee
  3  GROUP BY division_id, ROLLUP(division_id, job_id);

DIV JOB SUM(SALARY)
--- --- -----------
BUS PRE      800000
SAL MGR      350000
SAL WOR      490000
SUP MGR      200000
BUS          800000
SAL          840000
SUP          200000
BUS          800000
SAL          840000
SUP          200000

10 rows selected.

SQL>
SQL>
SQL>
SQL> drop table employee;

Table dropped.

SQL>

Related examples in the same category

1.	Use avg, sum, max and count functions with group
2.	Using the GROUP BY Clause
3.	Must include a nonaggregate column in the SELECT list in the GROUP BY clause
4.	Use group by and avg
5.	GROUP BY may be used on a column without the column name appearing in the result set
6.	Grouping at Multiple Levels: group by more than one column
7.	Column sequence in the group by impacts the ordering
8.	Using the ORDER BY Clause to Sort Groups
9.	You don't have to include the columns used in the GROUP BY clause in your SELECT clause
10.	GROUP BY and HAVING clauses
11.	timing and auto tracing a select statement with group
12.	Group joined tables
13.	Group by case
14.	Group by course name then by begin date
15.	Group and count employeem and display only if its count is more than 4
16.	Count all employees by even/odd employee id

Using a Column Multiple Times in a GROUP BY Clause : GROUP BY « Select Query « Oracle PL / SQL

Related examples in the same category