Have a running total of costs for sql call to datatable

Question

I'm creating a datatable using the sql call:

 SqlDataAdapter Adp = new SqlDataAdapter("select CONVERT(DATETIME, IncDate, 103) AS IncDate, SUM(IncCost) AS IncCost from Incomings GROUP BY CONVERT(DATETIME, IncDate, 103) ORDER BY CONVERT(DATETIME, IncDate, 103)", con);

which at the moment outputs

03/04/2016 - 13
05/04/2016 - 26
08/04/2016 - 5
13/04/2016 - 16
23/04/2016 - 23

I'm wondering how I can change my SQL query so that each cost is added to the previous one ie a running total. This would look something like

03/04/2016 - 13
05/04/2016 - 39
08/04/2016 - 44
13/04/2016 - 60
23/04/2016 - 83

Still no working solution as of yet and not sure how to go about it. Thanks in advance!

Possible duplicate of [SQL Server Cumulative Sum by Group](http://stackoverflow.com/questions/17971988/sql-server-cumulative-sum-by-group?rq=1). you can use subquery to achieve this — esiprogrammer, Mar 09 '16 at 00:03
thank you for this, although I got the error "Column 'Incomings.IncDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" — Stuart, Mar 09 '16 at 00:22
You can do that using subquery as it described in the answer of above question, so your query would be something like this: select CONVERT(DATETIME, IncDate, 103) AS IncDate, SUM(IncCost) AS IncCost, (select sum(IncCost) from Incomings t2 where t2.IncDate<=Incomings.IncDate ) as sumid from Incomings GROUP BY CONVERT(DATETIME, IncDate, 103) ORDER BY CONVERT(DATETIME, IncDate, 103) — esiprogrammer, Mar 09 '16 at 00:22
I executed the same query on my SSMS it works. what's your SQL Version? — esiprogrammer, Mar 09 '16 at 00:30
I'm using visual studio 2015 so whichever one is linked with that, the latest one I'm presuming — Stuart, Mar 09 '16 at 00:31
If want to do this in a SQL Query (which I recommend) you might want to learn to use the SQL Development tools. I suggest you open SQL Server Management Studio, connect and enter the query posted. — Nick.Mc, Mar 09 '16 at 02:08

Josh · Accepted Answer · 2016-03-11T15:27:49.650

3

You can try this and tweak as needed. It's easier to see what is happening if you do this in SSMS.

SELECT MyTable.IncDate AS IncDate, SUM(MyTable.IncCost) OVER (ORDER BY MyTable.IncDate) AS IncCost FROM
(
SELECT 
    CONVERT(DATETIME, IncDate, 103) AS IncDate, 
    SUM(IncCost) AS IncCost
FROM 
    Incomings
GROUP BY CONVERT(DATETIME, IncDate, 103)
) AS MyTable
ORDER BY MyTable.IncDate

Explanation: The inner SELECT statement allows us to do the grouping and get everything setup so the data is in the format that we want. Then we use the inner select like a table to do the SUM and OVER that allows us to get the running total.

edited Mar 11 '16 at 15:27

answered Mar 11 '16 at 01:01

Josh

1,724
13
15

thank you for this. I'm getting the error "Additional information: The function 'ROW_NUMBER' must have an OVER clause with ORDER BY". My SQL knowledge is very very very limited – Stuart Mar 11 '16 at 01:15
thanks for your continued help. I'm now getting "Additional information: ORDER BY list of RANGE window frame cannot contain expressions of LOB type." whatever that means – Stuart Mar 11 '16 at 01:21
It means that you're storing the IncDate values in something like a TEXT column. This would be a little easier if you posted the table schema. With that said. I've edited again to take that into account. – Josh Mar 11 '16 at 01:25
thank you for this. I'm using the data to create a chart: data1[i, 0] = Dt.Rows[i]["IncDate"]; data1[i, 1] = Dt.Rows[i]["IncCost"]; and when I call IncDate and IncCost, it is showing up the original data that I have. I tried changed it to MyTable.IncCost, but no joy – Stuart Mar 11 '16 at 01:37
thank you again, I think it's close. Although apparently "Column 'IncCost' does not belong to table ." – Stuart Mar 11 '16 at 01:51
Refactored this to make it a lot easier to read and implement. Changed the aliasing to give you two columns (IncDate and IncCost) – Josh Mar 11 '16 at 02:55
Thank you Josh it worked perfectly, you are a legend! I'll be able to award the bounty tomorrow – Stuart Mar 11 '16 at 03:17
I also think I understand what is happening but not completely – Stuart Mar 11 '16 at 03:17
Final thing, this is very close to working although the dates seem to be a day off. So any ones for the 8th April and the 30th of April are showing up as the 7th April and 29th instead. thank you for your continued help josh! – Stuart Mar 11 '16 at 03:27
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/105959/discussion-between-josh-and-stuart). – Josh Mar 11 '16 at 04:19
Hi Josh, quick one. Is this type of query known as a correlated subquery? – Stuart Mar 31 '16 at 14:57
No. In order to be a correlated subquery the inner query must use values from the outer. This is the other way round. – Josh Mar 31 '16 at 16:13

score 0 · Answer 2 · answered Mar 09 '16 at 05:50

0

If you are using ssms-2012 or above then try this query:

select CONVERT(DATETIME, IncDate, 103) AS IncDate, SUM(IncCost) over (order by IncDate) AS IncCost from Incomings GROUP BY CONVERT(DATETIME, IncDate, 103) ORDER BY CONVERT(DATETIME, IncDate, 103)

answered Mar 09 '16 at 05:50

Vimal Vataliya

61
3

thank you for this although I'm getting the error "Column 'Incomings.IncDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" when I run it – Stuart Mar 09 '16 at 12:55
try this query select CONVERT(DATETIME, IncDate, 103) AS IncDate, SUM(IncCost) over (order by IncDate) AS IncCost from Incomings GROUP BY IncDate ORDER BY CONVERT(DATETIME, IncDate, 103) – Vimal Vataliya Mar 10 '16 at 04:35
No joy for this either. Thanks though – Stuart Mar 10 '16 at 16:16

Have a running total of costs for sql call to datatable

2 Answers2