oracle query sequential summation per rows
You can use SUM OVER function:
SELECT *,
SUM(Input + Output) OVER(PARTITION BY ID ORDER BY Date) AS Total
FROM table_a
Oracle add consecutive rows
You appear to want a cumulative sum. You can use the cumulative sum functionality in SQL;
select t.*, sum(t.score) over (order by t.empid)
from t;
Get sum of consecutive rows in one row
You need Lead function, which will show second row with first row and so on. Name it nxt_score. Then you can add score and nxt_score to get your output. You will also need nvl as for last row, the nxt_score will be null. So in that case, it will add the score with 0.
Remove other columns from select clause if you want.
SELECT
t1.*,
score + nvl(nxt_score, 0) AS cum_score
FROM
(SELECT
t.*,
LEAD(score, 1) OVER (ORDER BY emp_id) AS nxt_score
FROM table1 t
) t1
Oracle row wise Sum
Just add them up and include it in your query;
SELECT emp_no
, year
, january
, february
, march
, april
, (january + february + march + april) AS total
FROM employee_expense
ORDER BY emp_no, year
That should do it unless there's something else involved that you haven't mentioned.
Oracle SQL Join Data Sequentially
If i am reading this correctly you want to determine how many parts have been used. In your example it looks like you have 5 usages and with 5 orders coming to a total of 8 parts with the following orders having been used.
- 4412 - one part - one used
- 4111 - one part - one used
- 7812 - one part - one used
- 0393 - three
parts - two used
After a bit of hacking away I came up with the following SQL. Not sure if this works outside of your sample data since thats the only thing I used to test and I am no expert.
WITH data
AS (SELECT *
FROM (SELECT *
FROM sub_b1
join (SELECT ROWNUM rn
FROM dual
CONNECT BY LEVEL < 15) a
ON a.rn <= sub_b1.orderqty
ORDER BY receivedate)
WHERE ROWNUM <= (SELECT SUM(useqty)
FROM sub_b2))
SELECT sub_b1.ordernum,
partnum,
receivedate,
orderqty,
usage
FROM sub_b1
join (SELECT ordernum,
Max(rn) AS usage
FROM data
GROUP BY ordernum) b
ON sub_b1.ordernum = b.ordernum
Query Oracle for 12 month moving total (SUM) of data
To get the desired result you need to order by year_dt column or combination of year_dt and month_dt columns:
select month_dt
, year_dt
, average_total_rainfall
, sum(average_total_rainfall) over(order by year_dt, month_dt
rows between 12 preceding
and current row) res
from rainfall_data
MONTH_DT YEAR_DT AVERAGE_TOTAL_RAINFALL RES
---------- ---------- ---------------------- ----------
1 1995 4,31 4,31
2 1995 1,932 6,242
3 1995 3,733 9,975
4 1995 4,216 14,191
5 1995 3,721 17,912
6 1995 8,379 26,291
7 1995 6,028 32,319
8 1995 7,918 40,237
9 1995 3,516 43,753
10 1995 5,623 49,376
11 1995 1,813 51,189
12 1995 1,881 53,07
1 1996 2,625 55,695
2 1996 1,165 52,55
3 1996 9,374 59,992
4 1996 2,84 59,099
5 1996 2,538 57,421
6 1996 5,952 59,652
7 1996 6,562 57,835
8 1996 8,428 60,235
When you are ordering only by month_dt the rows will be processed by analytic function in the following order:
month_dt year_dt average
--------------------------
1 1995 4.31
1 1996 2.625
1 1997 2.384
1 1998 3.205
1 1999 5.353
1 2000 2.217
1 2001 2.005
1 2002 1.179
1 2003 0.172
1 2004 1.769
1 2005 0.958
1 2006 1.48
1 2007 3.543
producing result that doesn't meet your expectations.
How to sum the values of a column for several rows?
it's unsolvable in bigdata ( > 100 millions rows)
SELECT
d[1] AS s,
d[-1] AS e,
arraySum(c) AS sm
FROM
(
SELECT
arraySplit((x, y) -> (NOT y), d, n) AS dd,
arraySplit((x, y) -> (NOT y), c, n) AS cc
FROM
(
SELECT
groupArray(date) AS d,
groupArray(ne) AS n,
groupArray(change) AS c
FROM
(
SELECT *
FROM mytable
ORDER BY rn ASC
)
)
)
ARRAY JOIN
dd AS d,
cc AS c
┌─s──────────┬─e──────────┬───────────sm─┐
│ 2008-12-07 │ 2009-04-26 │ -725166328 │
│ 2013-07-06 │ 2015-06-08 │ -15224653176 │
└────────────┴────────────┴──────────────┘
Query to sum the previous values
sum(tot_hours) OVER (PARTITION BY empid ORDER BY empid ) AS tot_hours
Your ORDER BY is incorrect. If you want the running SUM on the TOT_HOURS, then you should order by tot_hours.
For example, the below query will calculate the running sum of salary of employees in each department:
SQL> SELECT deptno,
2 sal,
3 SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ) AS tot_sal
4 FROM emp
5 ORDER BY deptno;
DEPTNO SAL TOT_SAL
---------- ---------- ----------
10 1300 1300
10 2450 3750
10 5000 8750
20 800 800
20 1100 1900
20 2975 4875
20 3000 10875
20 3000 10875
30 950 950
30 1250 3450
30 1250 3450
30 1500 4950
30 1600 6550
30 2850 9400
14 rows selected.
SQL>
Update For duplicate values, the running total would be duplicate. To make it unique, use UNBOUNDED PRECEDING clause. For example,
SQL> SELECT empno, deptno,
2 sal,
3 SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS UNBOUNDED PRECEDING) AS tot_sal
4 FROM emp
5 ORDER BY deptno;
EMPNO DEPTNO SAL TOT_SAL
---------- ---------- ---------- ----------
7934 10 1300 1300
10 1300 2600
7782 10 2450 5050
7839 10 5000 10050
7369 20 800 800
7876 20 1100 1900
7566 20 2975 4875
7788 20 3000 7875
7902 20 3000 10875
7900 30 950 950
7521 30 1250 2200
7654 30 1250 3450
7844 30 1500 4950
7499 30 1600 6550
7698 30 2850 9400
15 rows selected.
SQL>
Gaps and Islands - How to Sum Each Group of Consecutive Rows by ID
You are only interested with series of adjacent "CTG" rows. I think that the simplest approach is a window count of non-"STG" values to define the groups, then filtering and aggregation:
select
id,
min(tmsp) tmsp,
min(eff_dt) eff_dt,
sum(datediff(day, eff_dt, end_eff_dt)) sum_eff_days
from (
select
p.*
sum(case when cd = 'STG' then 0 else 1 end)
over(partition by id order by tmsp) grp
from #postchg_rows p
) p
where cd = 'STG'
group by id, grp
Select ranges (min, max) only for a sequence of numbers +1
In Oracle version 12.1 or later, you can use match_recognize like so:
with
my_table(col_number) as (
select 1 from dual union all
select 2 from dual union all
select 3 from dual union all
select 4 from dual union all
select 9 from dual union all
select 10 from dual union all
select 12 from dual
)
-- end of sample data (for testing only, not part of the solution)
select min_number, max_number
from my_table
match_recognize(
order by col_number
measures first(col_number) as min_number, last(col_number) as max_number
pattern (a b*)
define b as col_number = prev(col_number) + 1
);
MIN_NUMBER MAX_NUMBER
---------- ----------
1 4
9 10
12 12
Related Topics
Oracle SQL:Timestamps in Where Clause
SQL Server Error: Column Name or Number of Supplied Values Does Not Match Table Definition
Randomly Select a Row with SQL in Access
Update Table with a Subquery Which Is Returning More Than One Row
SQL Management Studio Won't Recognize a Table Exists After Scripted Create
Oracle Query Sequential Summation Per Rows
Comma-Separated Value Insertion in SQL Server 2005
How to Find Top Three Highest Salary in Emp Table in Oracle
How to Create Delphi 4 Structure to Map Column Names in Xls to Column Names in SQL
How to Group by the Each Week Upto Last Six Week Sundays Dates in SQL
Remove Duplicate Records Except the First Record in SQL
Pass String Variable Without Quotes in Query Vba
Oracle Regexp_Substr | Fetch String Between Two Delimiters
SQL Query Question: Select ... Not in