Oracle SQL Query to Find out Cumulative SUM and Duplicate Rows of a Table Records


  1. Query to find out Cumulative Sum from the table
Syntax:

SELECT <Column 1>, <Column 2>,
(SELECT SUM(<Column which you need cumulative Sum>)
FROM <Table Name> WHERE ROWID<= A.ROWID) CUMU_SAL
FROM <Table Name> A;

Example:
SQL> SELECT EMP_NAME, SAL,
(SELECT SUM(SAL) FROM EMP WHERE ROWID<= A.ROWID) CUMU_SAL FROM EMP A;

EMP_NAME                              SAL   CUMU_SAL
------------------------------ ---------- ----------
Rupal                                5000       5000
Hero                                 5500      10500
Jain                                 4000      14500
John                                 6000      20500
Riju                                 7000      27500
Sam                                  6500      34000
Kiran                                5500      39500
Peter                                6800      46300
King                                 7600      53900
Roshan                               5500      59400
Roshan                               5500      64900
David                                          64900

12 rows selected.
  1. Query to find out duplicate records from the table.
Syntax:

select * from <Table Name> a
where 1<(     select count(*)
from <Table Name>
where <Column Name1>=a. <Column Name1>
and    <Column Name2>=a. <Column Name2>
….);
Example:

SQL>  select * from emp a
where 1<(   select count(*)
from emp
where emp_name=a.emp_name);

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Roshan                                 30       5500
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google