Questions tagged [running-totals]
The running-totals tag has no usage guidance.
59 questions
1
vote
1
answer
36
views
Make sequential groups of rows that surpass a minimum sum
Let's say I have a table with a timestamp (ts) and a value (a).
With some sample data:
ts, a
1, 10
2, 30
3, 10
4, 20
5, 40
6, 10
7, 20
8, 30
etc
I would like to group rows, in ts order, by summing a ...
- 325
2
votes
2
answers
238
views
Group by running totals with reset
Sample Data
Expected Data
Need to append trades under an order in a record, was able to do that using string_agg, now I need to split the orders with a maximum value of 10. That is, the quantity for ...
- 23
2
votes
1
answer
256
views
Determining the overlap between payments and invoices
Given a table containing a list of invoices and a table containing a list of payments, I have the following problem:
Payments should be allocated to invoices up to the total amount of the invoice.
If ...
- 23
2
votes
2
answers
105
views
SQL Sum with break-out (audits on inventory)
I'm having some difficulty getting this mysql query to work. I have the following tables
create table items (
item_id int not null auto_increment,
item_name varchar(25),
primary key (...
- 23
2
votes
1
answer
989
views
Cumulative sum in a period of months
I have this table:
month_rep
fruits
harvested
2021-09-01
139
139
2021-10-01
143
11
2021-11-01
152
14
2021-12-01
112
9
2022-01-01
133
10
2022-02-01
145
12
2022-03-01
123
5
2022-04-01
111
4
...
- 23
2
votes
1
answer
1k
views
Cumulative sum by day with non-negative floor with PostgreSQL
Using PostgreSQL 12, I need to calculate the cumulative sum by day while filling in missing days. I also need to set a minimum value of 0 if any cumulative sum goes negative.
Background
There are two ...
- 121
1
vote
1
answer
898
views
Calculate running total of "consecutive days without views" for each group, that can restart multiple times within each group based on conditions
Summary
I'm working to create a counter metric that shows, for each client's slug, the number of consecutive days of zero views.
The counter should increase for each consecutive day that the client's ...
- 21
0
votes
1
answer
998
views
Is there any way to let Postgres know it can filter results and then calculate aggregations?
Given a table A with 10 million records and another table B with child items (one-to-many), I have to provide a consumable view with totals from several fields in B grouped by the FK of A.
None of the ...
- 1
0
votes
0
answers
80
views
Performant way to limit selected rows but make sure the sum of the rows is large enough
Here's a simplified version of the problem. You have a marketplace where users can publish how many items they have for sale and the price. You would like to buy 1 000 000 items, and you want to ...
- 53
0
votes
1
answer
198
views
Analytical function: sum the cumulative of previous column
Using an analytical function, I want that the column 'sum' contains the
cumulative of the previous column.
But my code gets the total sum of all encounters.
Here is both table and data for testing:
...
- 87
0
votes
0
answers
189
views
SQL YTD calculations adding periods without postings
I have a query which give me the YTD figures per company (client), account, IC and period for the periods in which financial postings have been performed. The table where I get this from is AAGCONSO ...
- 1
1
vote
1
answer
25
views
How do I show a running total from the previous records?
I wonder if someone can please assist.
I have the following code which basically inserts 10 records into a table. From this table I'm trying to show a running total for the amount of sales against the ...
- 25
1
vote
1
answer
700
views
Running sum total over 14 days for each date
In a database of transactions spanning about 50 customers over a period of 8 months, I need to find which customers have 0 total transactions over a consecutive 14-day period. The customer table (...
- 11
1
vote
2
answers
977
views
Sum all unique values from previous dates
Let's say, for simplicity sake, I have the following table:
id amount p_id date
------------------------------------------------
1 5 1 2020-01-01T01:00:00
2 10 ...
- 13
1
vote
1
answer
417
views
How to calculate running balance for many accounts?
please help me fix query below to calculate running balance correctly.
What I need to do is:
Calculate running balance (debit- credit) by account_no
Reset the balance when account_no changes
Reset ...
- 165
2
votes
2
answers
3k
views
How to create a running balance column based on account number?
I have a stored procedure that returns account statement for one or multiple accounts
Here is the part I have issues with: returning a running balance in each record based on account number.
My query ...
- 165
1
vote
1
answer
2k
views
Running total until specific condition is met
I have a question about running totals, I know there are several approaches. However I have slight modification of it and I'm struggling to find the right way.
So I have orders, each order has its ...
- 205
0
votes
1
answer
149
views
Need a query for accumulated values
I need query for accumulated values from particular column with specific time interval.
time | value
--------------------------
2020-02-26 09:30:00 | 5
2020-02-26 09:31:00 | 3 ...
2
votes
2
answers
128
views
Count Total time difference between selected row
My data contains an ID, status and time(hh:mm:ss).
I need to calculate the total time between every 'Location' and 'Out of tunnel' status
Current table
ID || time || Status
...
- 63
-1
votes
1
answer
38
views
Problem in mysql database!
how to create a view of this query in (mysql) database.
SET @variable = 0;
-- Without Pagination
SELECT
id,
`credit`,
`debit`,
@variable := @variable + (`credit` - `debit`) `Balance`
...
- 1
-1
votes
1
answer
77
views
TSQL Running Total but only based on Logic
Just been tasked to work on an old system that's running on SQL2008r2.
Need to audit the number of reserv earned and compare against the number of ticks taken.
So here's a sample dataset - Expected ...
- 513
0
votes
1
answer
66
views
Calculating Running Total in SQL
I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb.
I have Site data ...
1
vote
2
answers
670
views
How can I calculate the loan balance based on following data set
There are two loans 12345, 54321.
I want to arrive at 'New Column' which is calculated column based on Balance and Transaction_amount.
Also, need to calculate till maturity date.
CREATE TABLE #Test (...
14
votes
3
answers
14k
views
How to get the last not-null value in an ordered column of a huge table?
I have the following input:
id | value
----+-------
1 | 136
2 | NULL
3 | 650
4 | NULL
5 | NULL
6 | NULL
7 | 954
8 | NULL
9 | 104
10 | NULL
I expect the following ...
- 2,129
2
votes
2
answers
136
views
Calculate change in value window function when the identifier has dropped of the current period
I was wondering how can i got about finding the change in value from one period to another but in my scenario the identifier has dropped off the current period.
I'm think this could be done with a ...
- 133
0
votes
1
answer
473
views
Applying Multiple Window Functions in 1 SELECT using different partition
I was wondering if there is anything wrong with using this method.
I have a working demo of what i'm trying to do below
In the SELECT statement I have used 2 OVER clauses.
Is there any risk that ...
- 133
1
vote
2
answers
2k
views
I have a beginning balance I need to calculate ending balance running total by month rows
I have been searching this site and others trying to find an answer to this. I tried various things but cannot get my head around an answer so here goes.
Here my data:
What I need is to start with ...
- 11
1
vote
2
answers
4k
views
Subtracting sum of previous rows
I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause.
I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total ...
- 11
5
votes
1
answer
675
views
Cumulative duration between value resets
--DROP TABLE Taco_Val;
CREATE TABLE Taco_Val
(
ID INT IDENTITY(1,1),
AuditID VARCHAR(5),
CreditID VARCHAR(10),
TS DATETIME,
Val BIT
);
INSERT INTO ...
- 109
1
vote
1
answer
2k
views
Distribute multiple payments to invoice lines
I'm having a problem allocating payments to invoice lines.
We’re using MsSql 2014
Data looks like this:
Invoice lines table (sales):
lineId invoiceId value
1 1 100
2 ...
- 11
0
votes
1
answer
2k
views
How to calculate conditional running additions in SQL Server 2012
I have a table with below mentioned structure
create table #test
(
ID int identity(1,1),
A int,
B int
);
and having data as
ID A B
1 9 NULL
2 9 1
3 9 2
4 8 NULL
5 8 4
6 8 ...
- 103
10
votes
2
answers
3k
views
Calculating stock quantity based on change log
Imagine that you have the following table structure:
LogId | ProductId | FromPositionId | ToPositionId | Date | Quantity
---------------------------------------------------------------...
- 103
2
votes
2
answers
7k
views
Add cumulative sum to time-series query PostgreSQL 9.5
I wrote the query that gives me time-series over some date range and interval that shows revenue for each time interval:
SELECT
interval_date,
coalesce(campaign_revenue,0) AS campaign_revenue,...
- 219
0
votes
1
answer
35
views
Using OLAP-specs to compute selective running totals
Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value <> 0, and value is just some number. I want to build a query that ...
- 155
0
votes
1
answer
38
views
Running total from another field
I have a table just like that:
date sales rest
1/1/2017 52 0
2/1/2017 30 0
3/1/2017 10 200.
I only have the rest in the last date and i want to make it like this:
...
- 1
1
vote
2
answers
1k
views
Running ratio of unique counts to total counts
Any ideas on how to code a running ratio of counts (unique and total) for ordered observations (rows) in t-SQL (SQL Server 2017 Community Edition or higher)?
Given a data set, which is necessarily ...
- 111
2
votes
4
answers
7k
views
Running Total of a count per day in SQLite
I usually write a couple of very simple queries on a redshift database which used in a marketing visualization tool. And I repeat - VERY simple - I am a novice at this.
I am now faced with a new ...
- 51
3
votes
4
answers
7k
views
Set non-negative floor for rolling sum, in PostgreSQL
This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data,
CREATE TABLE foo
AS
...
- 64.7k
5
votes
2
answers
3k
views
Set non-negative floor for rolling sum, in SQL Server
I need to set a floor on the rolling sum calculation. For example, with
PKID NumValue GroupID
----------------------------
1 -1 1
2 -2 1
3 5 1
4 ...
- 313
11
votes
4
answers
7k
views
Reset Running Total based on another column
Am trying to calculate running total. But it should reset when the cummulative sum greater than another column value
create table #reset_runn_total
(
id int identity(1,1),
val int,
reset_val int,
...
- 1,399
1
vote
2
answers
2k
views
SQL to choose a price between two points in time
Given a time-series of datetime price changes and another disparate time-series of N events, how do I query the last known price at the time of each event without running N queries? Essentially, I ...
- 303
0
votes
1
answer
3k
views
CROSS APPLY query with CASE and running totals [closed]
I'm trying to make this business logic make sense, but I'm not having any luck.
The rule is I can have a sale with discounts from percentage and value. But the value is to be subtracted after ...
- 1
6
votes
1
answer
9k
views
Calculate Quantity Based on First in, First Out (FIFO)
I'm trying to get result of quantity based on FIFO, with 2 tables below:
Table Purchase:
| PO | Date | Quantity | Item |
|-------|--------------|----------|------|
| PO001 | 01-Jan-2016 ...
- 63
0
votes
1
answer
114
views
Getting Running Total from user defined function
I have the following query.
Select x1.TotalAmount, [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount,
x2.DocumentType
FROM table1 X1
INNER JOIN table2 x2 on t1.id = t2.t1id
Now how ...
- 137
7
votes
3
answers
1k
views
Is it possible to do this math in a view?
I have been tasked with creating a view for a client. Specifically it must be in a view. However, there is some math that I am not sure how to do within a view. I do not know if it is even possible. ...
- 617
2
votes
1
answer
6k
views
Calculating Running Total After Reset
I have a SQL Server 2012 table which has customer id, amount, and a reset columns. I’m trying to calculate the running total and also calculate the running total after reset. Once the reset "flag" is ...
- 173
3
votes
1
answer
14k
views
Calculate balance for every row by sequentially subtracting each row's amount from an initial sum
I'll explain the issue with an example.
A query that will select and show the Withdrawal Process I made and show the status of my deposit balance.
A table Deposit is created with columns TotalAmount, ...
- 137
5
votes
1
answer
10k
views
SQL Query for cumulative sum
I am experiencing problems with formulating a (relatively) simple SQL Query (SQL Server 2012 is used). We have a database which counts something up for certain users. Therefore we have a really simple ...
- 53
5
votes
1
answer
27k
views
Subtracting sum of previous rows' data from current row's data in SQL
I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty.
i.e.
The first data of qoh is the same of the ...
- 478
1
vote
2
answers
75
views
Need to sum a field in a row with same it and result needs to be cumulative in a field
This is my SQL Server 2008 R2 table:
ID Amount date_time
001 9.00 01/01/2015 01:01:00
001 20.00 01/02/2015 01:02:00
001 20.00 01/02/2015 01:03:00
002 20.00 01/01/2015 01:01:00
002 20.00 ...
- 11