Query for finding balance of credit and debit in SQL

Question

1.00/5 (1 vote)

See more:

Transaction Date Transaction Description Debit Credit balance
--------------- ---------------------- ----- ----- --------
01/04/2012 Savings 0.00 2,000.00 2,000.00

01/04/2012 withdrawal 750.00 0.00 1,250.00

16/04/2012 Savings 0.00 8,400.00 9,650.00

20/04/2012 Savings 0.00 400.00 1,00.50

What I have tried:

SELECT cust_id, acc_num, acc_date, acc_details, (SUM(debit)*-1) + SUM(credit) AS RunningBalance
FROM cust_account_t
GROUP BY cust_id, acc_num, acc_date, acc_details

Posted 3-Oct-16 7:22am

Mathiudi

Updated 3-Oct-16 9:26am

Richard MacCutchan

Add a Solution

Comments

[no name] 3-Oct-16 13:48pm

It would seem that you have forgotten to ask a question.

Mathiudi 3-Oct-16 13:55pm

I'm vert sorry. I'm under pressure here. Thanks for your reminders.

Can any body help me solve this?.

[no name] 3-Oct-16 13:58pm

Probably a lot of people can solve whatever it is that you are asking. But, we need a question or at least a description of some sort of a problem first. We are not mind readers over here.

Mathiudi 3-Oct-16 14:05pm

OK. What I want is, Let say on 1/4/2014 a customer made a deposit $100,000, If we are to print a statement for that customer, the statement should read below and so forth:

Date Details Debit Credit Balance
1/4/2014 Deposit 0.00 100,000.00 100,000.00

[no name] 3-Oct-16 14:25pm

Okay.... seems to be a language barrier thing. The table definition and your query do not match, none of your comments are questions, there is exactly zero questions in your posting anywhere, no description of a problem, no examples of input data, no example of the output that you get when you run your query. I do not know what it is the you expect from people based on the conflicting and wrong information that you have given us to work with.

2 solutions

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)

OriginalGriff · Accepted Answer · 2016-10-03T08:03:00

The problem is almost certainly that your GROUP BY clause contains too many columns - and that is normally because you don't understand what GROUP by does, and tried to get round the "Column 'name' is invalid in the select list because..." error by just adding columns to the group by in order to select them. Without a lot more info on exactly what you are trying to achieve we can't be at all specific, but this may help you to work it out on your own: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

Wendelius · Accepted Answer · 2016-10-03T09:26:00

If I understand the question correctly, one simple way is to use running calculations introduced in SQL Server 2012. Consider the following example

SQL

CREATE TABLE #Transactions (
   TransactionDate        date,
   TransactionDescription varchar(100),
   Debit                  decimal,
   Credit                 decimal
);

INSERT INTO #Transactions VALUES 
('04/01/2012', 'Savings',    0.00,   2000.00),
('04/01/2012', 'withdrawal', 750.00, 0.00),
('04/16/2012', 'Savings',    0.00,   8400.00),
('04/20/2012', 'Savings',    0.00,   400.00);

SELECT t.TransactionDate, t.TransactionDescription, SUM(t.Credit-t.Debit) OVER (ORDER BY t.TransactionDate, t.TransactionDescription) AS Balance
FROM #Transactions t
ORDER BY t.TransactionDate, TransactionDescription;

The query gives the following result

TransactionDate TransactionDescription Balance
--------------- ---------------------- -------
2012-04-01      Savings                2000
2012-04-01      withdrawal             1250
2012-04-16      Savings                9650
2012-04-20      Savings                10050

You can find some alternative ways in Calculating simple running totals in SQL Server[^]

Query for finding balance of credit and debit in SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0