How to sum the column order by specify column

Question

0.00/5 (No votes)

See more:

In my table has following

SQL

sno      date           desc            amt       credit        debit  total 
2	08/02/2012	PURCHASE A/C	23	  34500.00	0.00		
2	08/02/2012	SOMU	23	0.00	  34500.00	NULL	
3	08/02/2012	SALES A/C	24	  0.00	        6000.00	NULL	
3	08/02/2012	DEVA	24	0	  0.00	        NULL	NULL
4	08/03/2012	SBI	14	0.00	  1000.00	NULL	NULL
4	08/03/2012	Cash A/c	14	  1000.00	0.00	NULL	
4	08/03/2012	Cash A/c	14	  1000.00	0.00	NULL	
4	08/03/2012	Cash A/c	14	  1000.00	0.00	NULL

I want to sum of the Debit amount and Credit amount group by date how to do it?

SQL

sno      date           desc            amt       credit        debit  total 
2	08/02/2012	PURCHASE A/C	23	  34500.00	0.00		
2	08/02/2012	SOMU	23	0.00	  34500.00	NULL	
3	08/02/2012	SALES A/C	24	  0.00	        6000.00	NULL	
3	08/02/2012	DEVA	24	6000.00	  0.00	        NULL	NULL
4       08/02/2012      Total           null      69000         6000    null
5       08/02/2012      balance         null      9000          null    null
4	08/03/2012	SBI	14	0.00	  1000.00	NULL	NULL
4	08/03/2012	Cash A/c	14	  1000.00	0.00	NULL	
4	08/03/2012	Cash A/c	14	  1000.00	0.00	NULL	
4	08/03/2012	Cash A/c	14	  1000.00	0.00	NULL
5    08/03/2012        Total           null      4000          0.00	null
 6     08/03/2012       balance         null      4000          0.00    null

How to get the total amount for order by date.
Thank you

Posted 3-Aug-12 0:35am

devausha

Updated 3-Aug-12 5:11am

v3

Add a Solution

Comments

Santhosh Kumar Jayaraman 3-Aug-12 7:06am

u mean
1.get total debit and credit.
2. Get balance by subtracting total debit and credit
?

devausha 3-Aug-12 8:23am

Yes you are correct. I got the total amount by using
sum(debit),sum(credit) group by reportdate.But i want display it next to the specify column like in questions, now all the total amount rows are displays last. How to set it under corresponding date.

Philip Stuyck 3-Aug-12 9:23am

I don't quite understand what the result should look like, maybe you can provide an example of how the result of the query should look like.

devausha 3-Aug-12 11:06am

I try to get the total debit and credit group by report date. And then I display that's like one row and the total is display next to the last row of the correspoding date like questions

Santhosh Kumar Jayaraman 3-Aug-12 12:08pm

Check my solution

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)

Philip Stuyck · Accepted Answer · 2012-08-03T00:46:00

Solution 1

select date, sum(debit), sum(credit) from table
group by date

Posted 3-Aug-12 0:46am

Philip Stuyck

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-03T06:07:00

Solution 2

SQL

With CTE_Test
as
(select sno,[date],[desc],amt,creit,debit,Total,Row_number() over (PARTITION By date order by sno) as rowno
from temp_Table
union all
Select MAX(sno)+1,[date],'Total',null,SUM(creit),SUM(Debit),Null,1 from temp_table group by [date]
union all
Select MAX(sno)+2,[date],'Balance',null,SUM(creit)-SUM(Debit),null,Null,1 from temp_table group by [date])
Select * from cte_test order by date,sno

Posted 3-Aug-12 6:07am

Santhosh Kumar Jayaraman

Comments

devausha 4-Aug-12 2:38am

Thank you So much for your help. It is working nice.

Santhosh Kumar Jayaraman 4-Aug-12 2:40am

Welcome