clause

ROW_NUMBER clause in Oracle SQL – PLSQL

November 21, 2012 by techhoneyadmin

The ROW_NUMBER clause in Oracle SQL / PLSQL is basically a windowing clause and is used to assign a unique row number to fetched records based on an ordered list. ROW_NUMBER clause starts numbering from 1.

ROW_NUMBER clause can be used with and without PARTITION BY clause.

Syntax for using the ROW_NUMBER clause without PARTITION BY clause in Oracle SQL / PLSQL is;
SELECT column(s)
ROW_NUMBER () OVER (ORDER BY column)
FROM table_name;

Syntax for using the ROW_NUMBER clause with PARTITION BY clause in Oracle SQL / PLSQL is;
SELECT column(s)
ROW_NUMBER () OVER (PARTITION BY column ORDER BY column)
FROM table_name;

Example 1:
Using ROW_NUMBER () clause without PARTITION BY clause

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Sales	10

Now, suppose we want to see all the records from the employee table and also want to assign a unique row number to each row, then we can achieve the same as:

SELECT ROW_NUMBER () OVER (ORDER BY e.commission) SNo
       ,e.*
FROM employee e;

We will get the following result:

SNo	Employee_ID	Employee_Name	Department	Salary	Commission
1	104	Emp D	Support	30000	5
2	108	Emp H	Sales	12000	10
3	105	Emp E	Sales	32000	10
4	106	Emp F	Sales	20000	10
5	107	Emp G	Sales	12000	10
6	101	Emp A	Sales	10000	10
7	102	Emp B	IT	20000	20
8	103	Emp C	IT	28000	20

Here we can see that we have retrieved all the rows from employee table and also assigned a unique row number as ‘SNo’, for convenience.

Example 2:

Using ROW_NUMBER () clause with PARTITION BY clause

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Sales	10

Now, suppose we want to see all the records from the employee table grouped by department and also want to assign a unique row number to each row, then we can achieve the same as:

SELECT ROW_NUMBER () OVER (PARTITION BY e.department ORDER BY e.commission) SNo
       ,e.*
FROM employee e;

We will get the following result:

SNo	Employee_ID	Employee_Name	Department	Salary	Commission
1	102	Emp B	IT	20000	20
2	103	Emp C	IT	28000	20
1	101	Emp A	Sales	10000	10
2	108	Emp H	Sales	12000	10
3	105	Emp E	Sales	32000	10
4	106	Emp F	Sales	20000	10
5	107	Emp G	Sales	12000	10
1	104	Emp D	Support	30000	5

Here we can see that we have retrieved all the rows from employee table and also assigned a unique row number to each row department wise i.e. as soon as the department changes the row number starts from 1 till other department is encountered.

UNBOUNDED PRECEDING Clause with PARTITION BY in Oracle SQL – PLSQL

November 19, 2012 by techhoneyadmin

The UNBOUNDED PRECEDING is a windowing clause which defines the aggregation window i.e. the extent of rows to be used in aggregation. It tells oracle, the extent from where the rows are to be aggregated in the subgroup.

Syntax for the UNBOUNDED PRECEDING clause with PARTITION BY in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function () OVER (PARTITION BY column(s) ORDER BY column(s) ROWS UNBOUNDED PRECEDING)
FROM table_name
GROUP BY (column(s));

Example:
Using the UNBOUNDED PRECEDING clause with partition by
Suppose we have a table named ‘employee’ as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10

If we write our query as:

SELECT department
       ,employee_name
       ,SUM(salary) EMPLOYEE_SALARY
       ,SUM(SUM(Salary)) OVER (PARTITION BY department ORDER BY department
                         ROWS UNBOUNDED PRECEDING) AS TOTAL_SALARY
FROM employee
GROUP BY department, salary, employee_name
ORDER BY department, employee_name;

We will get the following result:

Department	Employee_Name	Employee_Salary	Dept_Salary
IT	Emp B	20000	20000
IT	Emp C	28000	48000
Sales	Emp A	10000	10000
Sales	Emp E	32000	42000
Sales	Emp F	40000	82000
Support	Emp D	30000	30000

Let’s observe the records (especially the ‘Dept_Salary’ column) fetched to understand the UNBOUNDED PRECEDING clause.

1^st and 2^nd record (for Emp B and Emp C) pertain to ‘IT’ department, the Dept_salary column for the second records gives us the total of the salary being given to the ‘Emp B’ and ‘Emp C’ in ‘IT’ Department
3^rd, 4^th and 5^th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
The Dept_salary column of the 4^th and 5^th record tells us the total of salary being given to ‘Emp A’ and ‘Emp E’ and ‘Emp A’, ‘Emp E’ and ‘Emp F’ respectively in ‘Sales’ department.
The 6^th record corresponds to ‘Emp D’ in ‘Support’ department, the Dept_salary column for this record gives the total of salary being given to ‘Emp D’in ‘Support’ department.

UNBOUNDED PRECEDING Clause without PARTITION BY in Oracle SQL – PLSQL

November 19, 2012 by techhoneyadmin

Syntax for the UNBOUNDED PRECEDING clause without PARTITION BY in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function () OVER (ORDER BY column(s) ROWS UNBOUNDED PRECEEDING)
FROM table_name
GROUP BY (column(s));

Example:

Using the UNBOUNDED PRECEDING clause without PARTITION BY

Suppose we have a table named ‘employee’ as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10

If we write our query as:

SELECT department
       ,employee_name
       ,SUM(salary) EMPLOYEE_SALARY
       ,SUM(SUM(Salary)) OVER (ORDER BY department ROWS UNBOUNDED PRECEDING) AS CUMULATIVE_SALARY
FROM employee
GROUP BY department, salary, employee_name
ORDER BY department, employee_name;

We will get the following result:

Department	Employee_Name	Employee_Salary	Cumulative_Salary
IT	Emp B	20000	20000
IT	Emp C	28000	48000
Sales	Emp A	10000	58000
Sales	Emp E	32000	90000
Sales	Emp F	40000	130000
Support	Emp D	30000	160000

Let’s observe the records (especially the ‘Cumulative_Salary’ column) fetched to understand the UNBOUNDED PRECEDING clause.

1^st and 2^nd record (for Emp B and Emp C) pertain to ‘IT’ department, the cumulative_salary column for the second records gives us the total of the salary being given to the ‘Emp B’ and ‘Emp C’
3^rd, 4^th and 5^th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
The ‘cumulative_salary’ column in the 3^rd record tells us the total of the salary being given to ‘Emp B’, ‘Emp C’ and ‘Emp A’. Similarly the cumulative_salary culomn of the 4^th and 5^th record tells us the total of salary being given to ‘Emp B’, ‘Emp C’, ‘Emp A’ and ‘Emp E’ and ‘Emp B’, ‘Emp C’, ‘Emp A’, ‘Emp E’ and ‘Emp F’ respectively.
The 6^th record corresponds to ‘Emp D’ in ‘Support’ department, the cumulative_salary column for this record gives the total of salary being given to ‘Emp A’, ‘Emp B’, ‘Emp C’, ‘Emp D’, ‘Emp E, and ‘Emp F’.

ROLLUP Clause in Oracle SQL – PLSQL

November 8, 2012 by techhoneyadmin

In simple terms the ROLLUP clause is used to get the subtotal and grand total in a set of fetched records based on groups.

In other words we can say that ROLLUP clause extends the functionality of the GROUP BY Clause by returning rows containing a subtotal for each group along with a grand total for all groups

Syntax for the ROLLUP clause in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function ()
FROM table_name
GROUP BY ROLLUP(column(s));

Example:

Using the ROLLUP clause

Suppose we have a table named ‘employee’ as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10

If we write our query as:

SELECT employee_name
       ,count(*) Employees
       ,department
FROM employee
GROUP BY ROLLUP(department,employee_name);

We will get the following result:

Employee_Name	Employees	Department
Emp B	1	IT
Emp C	1	IT
	2	IT
Emp A	1	Sales
Emp E	1	Sales
Emp F	1	Sales
	3	Sales
Emp D	1	Support
	1	Support
	6

Let’s observe the records fetched to understand the ROLLUP clause:

1^st and 2^nd record (for Emp B and Emp C) pertain to ‘IT’ department
The 3^rd record is the subtotal for the IT department; it states that the total number of employees in ‘IT’ department is 2.
4^th, 5^th and 6^th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
The 7^th record is the subtotal for the ‘Sales’ department and tells us that there are 3 employees in ‘Sales’ department.
8^th record for Emp D corresponds to the “Support’ department
9^th record is the subtotal for the ‘Support’ department and tells us that there is only 1 employee in ‘Support’ department.
Finally the 10^th record is the grand total of all the records and tells us that there are total of 6 employees in ‘IT’, ‘Sales’ and ‘Support’ department combined together.

OVER Clause in Oracle SQL – PLSQL

November 7, 2012 by techhoneyadmin

In simple terms the OVER clause in Oracle SQL / PLSQL specifies the partition or order in which an analytical function will operate.

Syntax for the OVER clause in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function OVER (PARTITION BY column(s))
FROM table_name;

Example 1:

Using OVER clause

Suppose we have a table named ‘employee’ as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10

If we write our query as:

SELECT employee_id
       ,employee_name
       ,department
       ,COUNT(*) OVER (PARTITION BY department) Total
FROM employee;

We will get the following result:

Employee_Id	Employee_Name	Department	Total
103	Emp C	IT	2
102	Emp B	IT	2
106	Emp F	Sales	3
105	Emp E	Sales	3
101	Emp A	Sales	3
104	Emp D	Support	1

Here we have used the OVER clause to get ‘Total’ column where we have retrieved ‘2’ for ‘IT’ department as there are ‘2’ records available in employee table for ‘IT’ department similarly we have ‘3’ and ‘1’ records for ‘Sales’ and ‘Support’ departments.