Running Total Sum() Problem

Question

4.33/5 (2 votes)

See more:

SQL-Server-2005

I don't know why the last record retrieving false value in the balance column.
It should be -200..

select 
    s.ID, s.date as Date,
    s.invno as Invoice_No,
    s.Debit as Db,s.Credit as Cr,
    (
        select sum(Debit)-sum(Credit) 
        from 
            GL_Table t 
        where 
            t.ID <= s.ID
    ) AS Balance
from 
    GL_Table s 
where 
    invno = 123456

ID         Date             Invoice_No   Debit   Credit    Balance
1	2014-03-16 00:00:00.000	123456	0	400	-400
2	2014-03-16 00:00:00.000	123456	100	0	-300
3	2014-03-16 00:00:00.000	123456	50	0	-250
4	2014-03-17 00:00:00.000	123456	20	0	-230
8	2014-03-18 00:00:00.000	123456	30	0	-250

please ask for more info. if needed.

Thanks in advance

Posted 20-Mar-14 3:22am

Member 10685840

Updated 20-Mar-14 4:44am

v8

Add a Solution

Comments

King Fisher 20-Mar-14 9:31am

this is wrong .show your table structure with data.
.

Member 10685840 20-Mar-14 10:44am

I updated the Q.

Mike Meinz 20-Mar-14 11:18am

Thanks. Knowing the ID values was the key to the solution.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2014-03-20T04:44:00

My assumption is that there is a credit value of 50 for another invoice that has an ID value less than 8 and greater than 4.

I added AND t.Invno = s.Invno in the subquery's where clause to ensure only rows for the selected invoice are included in the subquery result.

I added order by id to ensure proper ordering of the result set.

SQL

select 
    s.id,s.date as Date,
    s.invno as Invoice_No,
    s.Debit as Db,
    s.Credit as Cr,
    (
      select sum(Debit)-sum(Credit) from GL_Table t 
      where t.ID <= s.ID AND t.Invno = s.Invno
     ) AS Balance 
     from GL_Table s where invno = 123456 order by id

My test data:

SQL

CREATE TABLE [dbo].[GL_Table](
    [ID] [int] NOT NULL,
	[Date] [date] NOT NULL,
	[InvNo] [int] NOT NULL,
	[Debit] [int] NOT NULL,
	[Credit] [int] NOT NULL
) ON [PRIMARY]
Go
insert into gl_table values (1,'2014-03-06',123456,0,400);
insert into gl_table values (2,'2014-03-06',123456,100,0);
insert into gl_table values (3,'2014-03-06',123456,50,0);
insert into gl_table values (4,'2014-03-17',123456,20,0);
insert into gl_table values (8,'2014-03-18',123456,30,0);
insert into gl_table values (7,'2014-03-18',123457,0,50);

Alternate Solution using Inner Join:

select 
    s.date as Date,
    s.invno as Invoice_No,
    s.Debit as Db,
    s.Credit as Cr,
   (sum(t.Debit)-sum(t.Credit)) as Balance  
From GL_Table s inner join GL_Table t on s.invno=t.invno and t.id<=s.id
Where s.invno = 123456 
Group by s.id,s.date,s.invno,s.debit,s.credit