PL/SQL :: Running Total For Duplicate Rows

Dec 19, 2012

I am trying to write an sql which shows the running total for records which has duplicate.

sample rows:

col1 col2 col3
1      A    2
1      A    2
1      A    2
1      B    3
1      B    3
1      C    5
1      D    2
1      D    2o

p required:

col1 col2 col3  cumulative_tot
1      A    2       2
1      A    2       2
1      A    2       2
1      B    3       5
1      B    3       5
1      C    5       10
1      D    2       12
1      D    2       12

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Running Total SUM (Amt) Over?

Apr 21, 2012

My example: I'm given an Allowance throughout the week. It happens to be 10 dollars but it can vary from day to day.I can create a running total with SUM(Amt) Over etc...This is the CUMUL column in the example below.

On certain days I've spent different percentages of the allowance. (The SPENT Column which is a field in the database)I can't manage to create the AMTLEFT column in the example below.The AmtLeft column seems to be a kind of running total that 'refers to itself' so this is where I'm stumped.

Week,Day,Amt,Cumul,Spent,AmtLeft
1,Mon,10,10,0%,10
1,Tue,10,20,50%,10
1,Wed,10,30,0%,20
1,Thu,10,40,0%,30

[code]...

My imaginary SQL would look something like at this point (if I have it right):

SELECT Week, Day, Amt, Sum(Amt) Over (Partition By Week, Order By Day) AS Cumul, Spent FROM AllowancesTable

How to get the last column AmtLeft?

View 14 Replies View Related

PL/SQL :: Running Total For Each Day - Nulls?

Oct 2, 2013

I'm on 11.2.0.3.  I want to write a query to get calculate a running total of incidents per day - this query will be used for an APEX line chart.Sample table and data:

create table sales (
  id number primary key,
  time_of_sale date,
  item varchar2(20));
insert into sales values (1, to_date('02-JAN-2013','DD-MON-YYYY'), 'book'); ....

View 6 Replies View Related

SQL & PL/SQL :: Monthly Running Total With Opening Balance

May 9, 2012

I want to generate the gapless montly running balance for the whole year.Data in the table "A" is as below.

code Date amount remarks
0101 30-SEP-10 1000 Opening
0101 01-OCT-10 1500
0102 25-OCT-10 500
0102 10-DEC-10 750
0101 26-JAN-11 450
0102 03-MAR-11 3000
0101 30-SEP-11 6000

Required output is
Code Date amount Balance
0101 31-OCT-10 1500 2500
0101 30-NOV-10 0 2500
0101 31-DEC-10 0 2500
0101 31-JAN-11 450 2950
-- -- -- continue
0101 30-SEP-11 6000 8950

Same output for code 0102. I have taken only 02 code for excample only. There are about 250 code in table.

View 14 Replies View Related

SQL & PL/SQL :: Rows Depends On A Total Field

Feb 24, 2012

I have a table TP having following data (Dashes used for space as i am unable to have proper alignment)

ID1-----TOT
1 ------- 5
2 ------- 7

I need a query that repeat the records depending on the field TP.TOT

e.g.

ID1 -----TOT
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7

View 5 Replies View Related

Backup & Recovery :: Running RMAN Commands In RUN File To Duplicate A Database

Dec 17, 2012

I have a RUN file within a script to duplicate a database. As part of this RUN file I also run the command configure controlfile autobackup off; as part of this RUN file. However, this command has turned off backup of the control file on the AUXILLARY database (i.e. production) as well as the TARGET database (test). Is this supposed to happen?

My run file is:

rman TARGET sys/XXXX@${DB}-SOURCE AUXILIARY /
RUN {
allocate AUXILIARY channel a1 type disk;
allocate AUXILIARY channel a2 type disk;
allocate AUXILIARY channel a3 type disk;
allocate AUXILIARY channel a4 type disk;

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: Get Total Number Of Rows Returned By Query?

Sep 17, 2010

DECLARE
l_query VARCHAR2(4000);
TYPE cursor_type IS REF CURSOR;

[Code].....

How can I get the total number of rows returned by the query?

I want to be able to check omething like c1.ROWS = 0

View 4 Replies View Related

How To Store Total No Of Updated Rows (number) In A Variable

Jun 21, 2011

how to store total no of updated rows (number) in a variable after executing an updation query using script

View 2 Replies View Related

Eliminate Duplicate Rows

Jan 27, 2009

I have to eliminate duplicate pairs from the following data set.

for ex. Adney Vaughan and Berkly Wassen appears in both AG1 and AG2. how can i get rid of these repititive rows?

AG1 ----------- AG2
Acton Wibert ---- Currier Barhydt
Adney Vaughan --- Luella Edmund
Adney Vaughan --- Berkly Wassen
Alden Anstruther --- Courtney Gavet
Ashley Alvord --- Saunders Buel
Aswin Wilbraham --- Dale Cooper
Barnum Sears --- Grayson Lightfoot
Berkly Wassen --- Luella Edmund
Berkly Wassen --- Adney Vaughan
Bersh Musgrave --- Derward Knight
Berthilda Darrell --- Broderick Reynold
Broderick Reynold --- Berthilda Darrell

View 1 Replies View Related

Delete Duplicate Rows?

Feb 6, 2011

I having a problem with duplicate rows with a query.

What I have so far is

SELECT D.Student_Id,
E.Latest_Reg_Date,
E.Attendance_Count,

[Code].....

View 1 Replies View Related

SQL & PL/SQL :: Update Duplicate Rows To 0?

Oct 25, 2011

getting sql query to get the result below.If the Key repeats then I need to set it to O if for each key New_link doesnot match.

My Present table

Key New_Link
1 4
3 2
3 5
5 1
5 1

RESULT

Key New_Link
1 4
3 0
5 1

View 5 Replies View Related

PL/SQL :: Duplicate Rows Removal

Jul 3, 2012

removing duplicate rows from a table.

We have the following table:

SSD@ermd> desc person_pos_history
Name Null? Type
------------------------------------------------------------------------ -------- ------------------------

PERSON_POSITION_HISTORY_ID NOT NULL NUMBER(10)
POSITION_TYPE_ID NOT NULL NUMBER(10)
PERSON_ID NOT NULL NUMBER(10)
EVENT_ID NOT NULL NUMBER(10)
USER_INFO_ID NUMBER(10)
TIMESTAMP NOT NULL DATE

We found out that few person_id's are repeating for a particular event (3):

select PERSON_ID, count(*)
from person_pos_history
group by PERSON_ID, EVENT_ID
having event_id=3
and count(*) > 1
order by 2

PERSON_ID COUNT(*)
---------- ----------
217045 356
216993 356
226198 356
217248 364

[Code]...

If we look at the 1st person id "217045", we can see that it is repeating 356 times for event id 3.

SSD@ermd> select POSITION_ASSIGNMENT_HISTORY_ID, POSITION_TYPE_ID, PERSON_ID,EVENT_ID, to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS')
2 from person_pos_history
3 where EVENT_ID=3
4 and person_id=217045
5 order by timestamp;

PERSON_POSITION_HISTORY_ID POSITION_TYPE_ID PERSON_ID EVENT_ID TO_CHAR(TIMESTAMP,'
------------------------------ ---------------- ---------- ---------- -------------------
222775 38 217045 03 2012-05-07 10:29:49
222774 18 217045 03 2012-05-07 10:29:49
222773 8 217045 03 2012-05-07 10:29:49

[Code]...

356 rows selected.It is safe to assume that the person id/event id with the earliest timestamp is the one that was loaded 1st, hence, the one we want to keep and the rest should be deleted.

sql to achieve the duplicate removal.

View 6 Replies View Related

SQL & PL/SQL :: How To Remove Duplicate Rows From Table

May 8, 2013

how to remove duplicate rows from table?

View 6 Replies View Related

SQL & PL/SQL :: How To Retrieve Duplicate Rows From A Table

Jun 14, 2010

How to retrieve duplicate rows from a table, suppose i don't know any column names. Without hard-coding column names I need to find.

View 5 Replies View Related

SQL & PL/SQL :: Joining But Avoiding Duplicate Rows

Oct 16, 2010

I have two tables A and B

CREATE TABLE A(EMP_ID NUMBER, EMP_NAME VARCHAR2(100))
CREATE TABLE B(EMP_ID NUMBER, EMP_ATT1 VARCHAR2(10), EMP_ATT2 VARCHAR2(10))
INSERT INTO A VALUES(1, 'ONE');
INSERT INTO A VALUES(2, 'TWO');
INSERT INTO A VALUES(3, 'THREE');

[Code]....

This query returns all the matching row of A and B

SELECT A.EMP_ID, A.EMP_NAME, B.EMP_ATT1, B.EMP_ATT2
FROM A
INNER JOIN B ON A.EMP_ID=B.EMP_ID

The output for this shows:

EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
2 TWO 2ATT1.1 2ATT2.1
3 THREE 3ATT1 3ATT2

The requirement is to avoid duplicate rows even if matched:

EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
3 THREE 3ATT1 3ATT2

View 8 Replies View Related

SQL & PL/SQL :: Calculating Sum From Duplicate Rows With Timestamp?

Sep 16, 2011

I am trying to find sum for one record for each partition but while taking that timestamp giving me bit trouble, i have tried to reproduce the table and some little data

CREATE TABLE TEST_COUNT
(END_TIME DATE
,SUCCESSFUL_ROWS NUMBER
,FAILED_ROWS NUMBER
,TBL_NAME VARCHAR (4)
,PARTITION_NAME VARCHAR (240) )

[code]....

View 11 Replies View Related

SQL & PL/SQL :: Duplicate Rows Displayed For A Column

Mar 30, 2013

column sid format 'a5'
column serial# format 'a10'
column mins_running format 'a15'
column sql_text format 'a100'
set linesize 200
set pagesize 30

[Code]..

I am running this code, and the output shows multiple lines.

TRIM(S.SID) TRIM(S.SERIAL#) MINS_RUNNING SUBSTR(Q.SQL_TEXT,1,70)
---------------------------------------- ---------------------------------------- --------------- ----------------------------------------------------------------
700 46592 242.08 Select count(*) as count, case when count(*)>0 then 'FAIL' else
700 46592 242.08 'PASS' end as result
from (SELECT cv.code_value
FROM code_valu

[Code]...

Is there a way to wrap up the column for SQL_TEXT VARCHAR2(64) so that I can 1 row for the output?

View 14 Replies View Related

PL/SQL :: Procedure To Update Duplicate Rows?

Mar 14, 2013

In a table i have some duplicate rows

I can get it through this query : select PARTY_ID from XXWFS_CUSTOMER_EXT group by PARTY_ID having count (PARTY_ID) > 1;

Now for the records which i got for each duplicate row i want to update the second row with a specific value.. so that duplicate rows does not exist anymore

Ex: I got party id's 12, 14, 16, 18 two times each

Now as 12 is two times.. i want to update the second row of 12 with some x value same is the case for other values like 14,16, etc

how can i write a procedure for this

View 3 Replies View Related

SQL & PL/SQL :: To Remove Duplicate Rows From Output

Jun 14, 2011

I have a view and in that view i need to remove duplicate rows from output. For that i need to run select query in where clause of view if select query return true then we need to execute second condition.

my requirement in view like

And..........
And ((select count(*) from table A where conditions)=1 )then name is null
AND

in that code first we need to check first select query condition then we need to apply name is null condition. but i tried to run it but select query not run properly. because tables is used in View.

View 4 Replies View Related

View - Join Displays Duplicate Rows?

Apr 16, 2012

The view below creates, however displays duplicate rows. Why is this may I ask?

CREATE OR REPLACE VIEW customer_order_vw
AS
SELECT
a.customer_id,

[Code]....

View 1 Replies View Related

Duplicate Sequence Number For Identical Rows

Jul 5, 2010

I have a table:

Name
_____
Smith Street
Smith Street
John Street
Ed Street
Ed Street
Ed Street

and need to assign sequence numbers only when the record (Name) changes, e.g. :

Name Seq
_____ ____
Smith Street 1
Smith Street 1
John Street 2
Ed Street 3
Ed Street 3
Ed Street 3

I have experimented with row_number partition but then i just get the sequence returning to 1 when the name value changes.

If I grouped the records by Name I would like to have unique, sequential numbers: 1, 2, 3 but where there is the same name I would like the sequence to stop and the number to replicate?

View 9 Replies View Related

SQL & PL/SQL :: Delete The Duplicate Rows In A Table Without Using ROWID?

Mar 1, 2010

I want to delete the duplicate rows in a table without using ROWID.

I have the following data.

SNO SNAME SECTION
1 RAM A
2 SHYAM B
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F
5 RAJESH F
The Output Should be like this.

SNO SNAME SECTION
1 RAM A
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F

View 8 Replies View Related

SQL & PL/SQL :: How To Avoid Duplicate Rows From Being Inserted In The Table

Dec 21, 2009

I have one table in which I want to restrict some records from being inserted. I don't want to put any checked constraints. e.g. consider following table

transaction(
id number primary key,
txn_date timestamp(7),
payee varchar2(40),
amount number,
memo varchar2(40),
ref_num number
)

I want to write SQL which should not inset duplicate record.

e.g.

I have written one as bellow:

insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
(select count(*) from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12)=0;

Can I use exists/not exists, which query will be more appropriate. (Please consider that fields which I am using to filter out the duplicate transactions does not contain primary key.)

Can I write such SQL. Or do i check for duplicate rows one by one and then filter the duplicate records.

View 21 Replies View Related

SQL & PL/SQL :: Removing Duplicate Rows When Condition Is Matched

Mar 17, 2010

My requirement if id, join_date, join_time, result of table1 is matched with table2 at least one time then if repeating rows associated with the id should not come.Here is the test case.

create table table1
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
create table table2
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));

insert into table1 values (01, '20010101', '0500', 'PASS');
insert into table1 values (01, '20010102', '0501', 'FAIL');
insert into table1 values (02, '20010103', '0502', 'PASS');
insert into table1 values (03, '20010104', '0503', 'FAIL');
insert into table1 values (04, '20010105', '0504', 'PASS');
insert into table1 values (05, '20010106', '0505', 'FAIL');
[code]...

I have tried the below mentioned query, whether any better query is there than this because in real-time data have 2 millions of record in table 1 and 60 thousand in table2.

select distinct a.id, a.join_date, a.join_time, a.status
from table1 a, table2 b
where a.id = b.id
and (a.id, a.join_date, a.join_time, a.status) not in (select b.id, b.join_date, b.join_time, b.status
from table2 b)
and a.id = (
select distinct a.id
[code]....

View 20 Replies View Related

SQL & PL/SQL :: Join Running Forever - Returns No Rows

May 23, 2012

select rl.org_rollup_skey from (select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486')) p
INNER JOIN IA.DIM_ORG_HIER h
ON p.fc_org_skey = h.desc_org_skey
inner join IA.FCT_FCST_SLS_ORG_ROLLUP rl
on h.GPRNT_ORG_SKEY = rl.org_rollup_skey

Above join is taking is running forever even as subquery

(select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486'))

returns no rows and this subquery give result in 10 seconds according to me Full query should not take more tha 20 secs.

View 1 Replies View Related

Application Express :: What Would Cause Oracle To Insert Duplicate Rows Into A Table

May 2, 2013

What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.

View 9 Replies View Related

Server Utilities :: Why Duplicated Rows When A PK Is Present Running Sqlldr

Dec 10, 2010

Why duplicated rows when a PK is present running Sqlldr?

View 1 Replies View Related

Performance Tuning :: Servers Will Be Running SELECT Which Returns Zero Rows All Time

Feb 11, 2011

Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.

Original query and plan

SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ;
[code]....

Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.

View 6 Replies View Related

Performance Tuning :: Get Number Of Rows Processed While Update Statement Is Still Running

Aug 25, 2010

Is there any way i can Get how many rows are processing with UPDATE statement while the Update statement is still running.

View 2 Replies View Related

PL/SQL :: Total And Grand Total

Mar 26, 2013

I'm running a query like the below but now i would like to make the last line actually say Grand Total. Instead of just total.

SELECT   decode (grouping (farinva_invh_Code),0,null,'Total') farinva_invh_Code,
         --decode ( grouping (amt),0,null,'GrantTotal')Grant_Total,
         farinva_invh_Code,
         spriden_id,
         --spriden_last_name "last Name"

[Code]....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved