-
Cumulative addition of column values in SQL
I have the following query...
SQL> select tkt_no, shift_no, t_date, line_no, prod_time
2 from mcd_jobcarddirect_dt;
TKT_NO SH T_DATE LINE_NO PROD_TIME
--------- -- --------- ---------- ---------
1 1 20-JUN-03 1 335
1 1 20-JUN-03 1 335
1 1 20-JUN-03 1 335
Now the PROD_TIME field here is displaying actual values. I want here that PROD_TIME field should get added by it's previous value for the group of TKT_NO, SHIFT_NO, T_DATE For e.g. it should look like this
TKT_NO SH T_DATE LINE_NO PROD_TIME
--------- -- --------- ---------- ---------
1 1 20-JUN-03 1 335
1 1 20-JUN-03 1 670
1 1 20-JUN-03 1 1005
How should I do that. I tried to use group by on above 3 fields but SUM wont be useful for cumulative additions.
Amol
-
I dont think u can achive this is single query ( though not sure if any fn )...
Well ,if you want in single query, then possibly u could have a dummy table or a COL in same table which hold a sequential numbering.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Code:
SELECT tkt_no, shift_no, t_date, line_no,
SUM(prod_time) OVER(PARTITION BY tkt_no, shift_no, t_date
ORDER BY tkt_no, shift_no, t_date)
FROM mcd_jobcarddirect_dt;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
Code:
SELECT tkt_no, shift_no, t_date, line_no,
SUM(prod_time) OVER(PARTITION BY tkt_no, shift_no, t_date
ORDER BY tkt_no, shift_no, t_date)
FROM mcd_jobcarddirect_dt;
Are you sure will that fit his requirement?
PS below, i have some thing like
Code:
us18:dba>desc Test_Serial
Name Null? Type
----------------------------------------------------- -------- ------------------
ID NUMBER
ID_DATE DATE
TIME_LAPSED NUMBER
us18:dba>select * from Test_Serial;
ID ID_DATE TIME_LAPSED
-------------------- --------- --------------------
2 20-JUN-03 1
2 20-JUN-03 1
2 20-JUN-03 1
and i want result set as
ID ID_DATE TIME_LAPSED
-------------------- --------- --------------------
2 20-JUN-03 1
2 20-JUN-03 2
2 20-JUN-03 3
I think this is what his Requirement is...
according to your suggested query, i get
Code:
us18:dba>SELECT Id, id_date, sum(time_lapsed) OVER(PARTITION BY ID,id_date order by Id,id_date)
2 from Test_Serial;
ID ID_DATE SUM(TIME_LAPSED)OVER(PARTITIONBYID,ID_DATEORDERBYID,ID_DATE)
-------------------- --------- ------------------------------------------------------------
2 20-JUN-03 1
2 20-JUN-03 1
2 20-JUN-03 1
us18:dba>
Am i missing any thing?
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
Am i missing any thing?
Your ID_DATE probably contains time fracton too, I suppose. So you actualy have three different dates for id=2. Try using
... OVER(PARTITION BY ID,TRUNC(id_date) order by Id,id_date) ...
instead.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
Your ID_DATE probably contains time fracton too, I suppose. So you actualy have three different dates for id=2. Try using
... OVER(PARTITION BY ID,TRUNC(id_date) order by Id,id_date) ...
instead.
With all rows for col ID_DATE being same i get,
Code:
us18:dba>SELECT Id, id_date, sum(time_lapsed) OVER(PARTITION BY ID,id_date order by Id,id_date)
2 from Test_Serial;
ID ID_DATE SUM(TIME_LAPSED)OVER(PARTITIONBYID,ID_DATEORDERBYID,ID_DATE)
-------------------- --------- ------------------------------------------------------------
2 20-JUN-03 3
2 20-JUN-03 3
2 20-JUN-03 3
Or
us18:dba>SELECT Id, id_date, sum(time_lapsed) OVER(PARTITION BY ID,trunc(id_date) order by Id,id_date)
2 from Test_Serial;
ID ID_DATE SUM(TIME_LAPSED)OVER(PARTITIONBYID,TRUNC(ID_DATE)ORDERBYID,ID_DATE)
-------------------- --------- -------------------------------------------------------------------
2 20-JUN-03 3
2 20-JUN-03 3
2 20-JUN-03 3
But not
Code:
ID ID_DATE SUM(TIME_LAPSED)OVER(PARTITIONBYID,ID_DATEORDERBYID,ID_DATE)
-------------------- --------- ------------------------------------------------------------
2 20-JUN-03 1
2 20-JUN-03 2
2 20-JUN-03 3
As per this requirement, i dont think single query can do any good..
BTW, did you check the query in ur test db, for such kinda o/p?
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Of course it can be in single SQL, jurij forgot windowing clause:
Code:
SELECT tkt_no, shift_no, t_date, line_no,
SUM(prod_time) OVER(PARTITION BY tkt_no, shift_no, t_date
ORDER BY tkt_no, shift_no, t_date
rows between unbounded preceding and current row)
FROM mcd_jobcarddirect_dt;
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
Originally posted by TomazZ
Of course it can be in single SQL, jurij forgot windowing clause:
Code:
SELECT tkt_no, shift_no, t_date, line_no,
SUM(prod_time) OVER(PARTITION BY tkt_no, shift_no, t_date
ORDER BY tkt_no, shift_no, t_date
rows between unbounded preceding and current row)
FROM mcd_jobcarddirect_dt;
Toma :
Super..aint knowing about that "unbounded preceding and current row"
Thanks..
Where can i find reference?
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by abhaysk
Where can i find reference?
In SQL manual. Windowing clause is (optional) part of any analytical function.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|