SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SUM two fileds from different rows from the same t
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  04:31:32  Show Profile  Reply with Quote
I would like to SUM two fileds from different rows from the same table but I don't know how to do that.

E.g.

BillingTransactionsIndex CreateDate UserType UserIndex TransType Reference Total Balance
2 6/5/2008 15:02 1 51 1 150 -288.2 -288.2
5 6/8/2008 11:55 1 51 1 157 -1.58674 -289.787

In the table above I want SUM fields Total and Balance for the first row and the the next row SUM 2nd row Total with 1st row Balance

Please help

Thanks

Edited by - tkotey on 06/25/2008 04:31:57

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  04:40:10  Show Profile  Reply with Quote
UPDATE c1
SET c1.Total=c1.Total + c1.Balance + ISNULL(c2.PrevBalance,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Balance) AS PrevBalance
             FROM YourTable 
             WHERE  UserType =c1.UserType
             AND UserIndex =c1.UserIndex
             AND TransType = c1.TransType
             AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2
Go to Top of Page

ganeshkumar08
Posting Yak Master

India
187 Posts

Posted - 06/25/2008 :  04:44:40  Show Profile  Send ganeshkumar08 an AOL message  Reply with Quote
Hello

If you have fixed number of known columns, which you want to sum use below query.

select Base.col1 + Child.col1 as 'MySum', Base.col2
From
Table1 Base
Inner Join Table1 Child On Base.col=Child.col


Edited by - ganeshkumar08 on 06/25/2008 05:05:05
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  04:51:06  Show Profile  Reply with Quote
quote:
Originally posted by ganeshkumar08

Hello

If you have fixed number of known columns, which you want to sum use below query.

select Base.col1 + Child.col1 as 'MySum', Base.col2
From
Table1 Base
Inner Join Table2 Child On Base.col=Child.col




Didnt understand how you think this works? What are table1 & table2 here?
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  04:59:54  Show Profile  Reply with Quote
Total Balance
row 1 90 90
row 2 23

I want to Insert the Balance for row 2 by adding the Total field of row2 with the Balance of row1. I don't want insert a new field. Sorry I am a newbie trying to understands the statements above. Thanks for your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  05:12:52  Show Profile  Reply with Quote
quote:
Originally posted by tkotey

Total Balance
row 1 90 90
row 2 23

I want to Insert the Balance for row 2 by adding the Total field of row2 with the Balance of row1. I don't want insert a new field. Sorry I am a newbie trying to understands the statements above. Thanks for your help.


what happend if you have more than two rows for same set of UserType UserIndex TransType values
Total Balance
row 1 90 90
row 2 23
row 3 15
row 4 20


do you want this?
Total Balance
row 1 90 90
row 2 23 90
row 3 15 23
row 4 20 15

or this

Total Balance
row 1 90 90
row 2 23 90
row 3 15 113
row 4 20 128
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  05:32:16  Show Profile  Reply with Quote
I want this

Total Balance
row 1 90 90
row 2 23 113
row 3 15 128
row 4 20 148

In row 2 to get the balance I SUM Balance(90) of row 1 with Total(23) of row 2 to get Balance (113) of row 2. Same thing is done for the next row by adding with Balance of previous row

Edited by - tkotey on 06/25/2008 05:35:32
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  05:41:41  Show Profile  Reply with Quote
thayts exactly what this does:-

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
             FROM YourTable 
             WHERE  UserType =c1.UserType
             AND UserIndex =c1.UserIndex
             AND TransType = c1.TransType
             AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  05:55:29  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

thayts exactly what this does:-

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
             FROM YourTable 
             WHERE  UserType =c1.UserType
             AND UserIndex =c1.UserIndex
             AND TransType = c1.TransType
             AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2




In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  05:57:51  Show Profile  Reply with Quote
quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

thayts exactly what this does:-

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
             FROM YourTable 
             WHERE  UserType =c1.UserType
             AND UserIndex =c1.UserIndex
             AND TransType = c1.TransType
             AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2




In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1


c1 and c2 are same table. i've just used two aliases for it. just replace YourTable with your actual table name and see.
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  06:10:41  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

thayts exactly what this does:-

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
             FROM YourTable 
             WHERE  UserType =c1.UserType
             AND UserIndex =c1.UserIndex
             AND TransType = c1.TransType
             AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2




In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1


c1 and c2 are same table. i've just used two aliases for it. just replace YourTable with your actual table name and see.



Oh OK. I tried the statement but all it did was to duplicate the totals into the balance
e.g.

The result was:

Total Balance
row 1 90 90
row 2 23 23
row 3 15 15
row 4 20 20

Instead of:

Total Balance
row 1 90 90
row 2 23 113
row 3 15 128
row 4 20 148
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  06:14:52  Show Profile  Reply with Quote
quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

thayts exactly what this does:-

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
             FROM YourTable 
             WHERE  UserType =c1.UserType
             AND UserIndex =c1.UserIndex
             AND TransType = c1.TransType
             AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2




In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1


c1 and c2 are same table. i've just used two aliases for it. just replace YourTable with your actual table name and see.



Oh OK. I tried the statement but all it did was to duplicate the totals into the balance
e.g.

The result was:

Total Balance
row 1 90 90
row 2 23 23
row 3 15 15
row 4 20 20

Instead of:

Total Balance
row 1 90 90
row 2 23 113
row 3 15 128
row 4 20 148



Are you sure you put correct column names inside the WHERE condition.
I just put them as seen from your sample data so i'm not sure its same you want.My assumption was that your all rows in a group will have same UserType,UserIndex & TransType with only difference in BillingTransactionsIndex value. if this is not correct, you need replace the WHERE condition with your actual rule.

Edited by - visakh16 on 06/25/2008 06:17:29
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  06:23:51  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

thayts exactly what this does:-

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
             FROM YourTable 
             WHERE  UserType =c1.UserType
             AND UserIndex =c1.UserIndex
             AND TransType = c1.TransType
             AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2




In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1


c1 and c2 are same table. i've just used two aliases for it. just replace YourTable with your actual table name and see.



Oh OK. I tried the statement but all it did was to duplicate the totals into the balance
e.g.

The result was:

Total Balance
row 1 90 90
row 2 23 23
row 3 15 15
row 4 20 20

Instead of:

Total Balance
row 1 90 90
row 2 23 113
row 3 15 128
row 4 20 148



Are you sure you put correct column names inside the WHERE condition.
I just put them as seen from your sample data so i'm not sure its same you want.My assumption was that your all rows in a group will have same UserType,UserIndex & TransType with only difference in BillingTransactionsIndex value. if this is not correct, you need replace the WHERE condition with your actual rule.



Here's a new sample data. I have included date

Date Total Balance
row 1 6/5/2008 9:45:24 AM 90 90
row 2 6/6/2008 9:45:24 AM 23 113
row 3 6/7/2008 9:45:24 AM 15 128
row 4 6/8/2008 9:45:24 AM 20 148

Below is the code


UPDATE BillingTransactions
SET BillingTransactions.Balance=BillingTransactions.Total + ISNULL(BillingTransactions_1.PrevBalance,0)
FROM BillingTransactions
OUTER APPLY (SELECT SUM(Balance) AS PrevBalance
             FROM BillingTransactions
             WHERE UserIndex =BillingTransactions.UserIndex
             AND CreateDate = BillingTransactions.CreateDate
             AND BillingTransactionIndex<BillingTransactions.BillingTransactionIndex) BillingTransactions_1

Edited by - tkotey on 06/25/2008 06:27:34
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  06:34:02  Show Profile  Reply with Quote
How can i know what are values for UserIndex,CreateDate and other fields? either post it or you rewrite the conditions as per your requirement.I assumed the WHERE conditions from your first sample data so i'm not sure that will be the one you want.
Please post clearly how will your data be & what will be rules otherwise you're really making it hard for someone trying to help you as we dont have access to your tabes and dont know how will be your data.
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  07:30:58  Show Profile  Reply with Quote
This is the current look of the database


Index   UserIndex   CreateDate            Total      Balance
1           23          6/5/2008 15:02    -199.98    0
2           23          6/7/2008 15:02     25.95     0
3           8           6/8/2008 15:02    -56.70     0
4           23          6/11/2008 13:00   -23.80     0
5           8           6/11/2008 16:00    10.00     0


And I want the calculation to be done in view. Basically calculate all the Totals for the same UserIndex between a StartDate and a EndDate.

Expected results below.


Index   UserIndex   CreateDate             Total      Balance
1           23          6/5/2008 15:02    -199.98    -199.98
2           23          6/7/2008 15:02     25.95     -174.03
3           8           6/8/2008 15:02    -56.70     -56.70
4           23          6/11/2008 13:00   -23.80     197.83
5           8           6/11/2008 16:00    10.00     -46.70


Thank you so much for help

Edited by - tkotey on 06/25/2008 07:32:07
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  07:46:43  Show Profile  Reply with Quote
UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevTotal,0)
FROM BillingTransactions b
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
             FROM BillingTransactions
             WHERE UserIndex =b.UserIndex
             AND Index<b.Index) c
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  08:00:20  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevTotal,0)
FROM BillingTransactions b
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
             FROM BillingTransactions
             WHERE UserIndex =b.UserIndex
             AND Index<b.Index) c




What is the 0 for in (c.PrevTotal,0)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  08:13:24  Show Profile  Reply with Quote
quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevTotal,0)
FROM BillingTransactions b
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
             FROM BillingTransactions
             WHERE UserIndex =b.UserIndex
             AND Index<b.Index) c




What is the 0 for in (c.PrevTotal,0)



it looks if there are any records coming before current for same UserIndex and in cases where none exists it returns a NULL. the isnull converts it to 0 and adds it to Total. this happens only for first occurance of UserIndex value.
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  08:16:44  Show Profile  Reply with Quote
I am trying to reproduce the data below. My table does not have the Balance and I am trying to add it then do the calculations. I tried your statement and still gave me the same result as before

Data for UserIndex 53

Record  	Date  	     Credit (BWP) Debit (BWP)  	Balance
Invoice # 151 	06/05/2008 	  	 288.20 	 BWP -288.21
Payment # 2 	06/10/2008 	 1,000.00 	  	 BWP 711.79
Invoice # 158 	06/10/2008 	  	 13.20 	         BWP 698.59
Invoice # 160 	06/12/2008 	  	 148.44 	 BWP 550.15
Invoice # 165 	06/16/2008 	  	 304.60 	 BWP 245.55
Invoice # 181 	06/17/2008 	  	 12,040.14 	 BWP -11,794.58
Payment # 4 	06/17/2008 	 300.00 	  	 BWP -11,494.58
Invoice # 185 	06/18/2008 	  	 39,688.18 	 BWP -51,182.76
Invoice # 188 	06/19/2008 	  	 112,987.20 	 BWP -164,169.96
Invoice # 191 	06/20/2008 	  	 14,679.63 	 BWP -178,849.59
Payment # 1 	06/20/2008 	 60.00 	  	         BWP -178,789.59
Invoice # 194 	06/21/2008 	  	 55,195.62 	 BWP -233,985.22
Invoice # 197 	06/22/2008 	  	 9,790.16 	 BWP -243,775.38
Invoice # 201 	06/23/2008 	  	 254.10 	 BWP -244,029.47
Payment # 2 	06/23/2008 	 78.00 	  	         BWP -243,951.47
Payment # 4 	06/23/2008 	 100.00 	  	 BWP -243,851.47
Payment # 5 	06/23/2008 	 30.00 	  	         BWP -243,821.47
Payment # 6 	06/23/2008 	 40.00 	  	         BWP -243,781.47
Payment # 7 	06/23/2008 	 57.00 	  	         BWP -243,724.47
Payment # 8 	06/23/2008 	 45.00 	  	         BWP -243,679.47



Right now my table is like this below. The credit and debit are on the same field which is Total. The balance is 0 throughout. Don't worry about the UType

Index   CreateDate             UType   UserIndex TransType      Total  Balance
3	2008-06-05 15:02:49.000	1	53	1	151	-288.2	0
6	2008-06-10 08:59:10.000	1	53	2	2	1000	0
7	2008-06-10 08:59:23.000	1	53	1	158	-13.2	0
9	2008-06-12 11:42:46.000	1	53	1	160	-148.439793	0
11	2008-06-16 03:00:00.000	1	53	1	165	-304.598546	0
13	2008-06-17 03:00:00.000	1	53	1	181	-12040.136172	0
18	2008-06-18 03:00:00.000	1	53	1	185	-39688.180278	0
21	2008-06-19 03:00:00.000	1	53	1	188	-112987.200625	0
24	2008-06-20 03:00:00.000	1	53	1	191	-14679.635	0
26	2008-06-20 18:20:12.000	1	53	2	1	60	0
28	2008-06-21 03:00:00.000	1	53	1	194	-55195.615937	0
31	2008-06-22 03:00:00.000	1	53	1	197	-9790.15872	0
34	2008-06-23 03:00:00.000	1	53	1	201	-254.1	0
36	2008-06-23 08:11:15.000	1	53	2	2	78	0
39	2008-06-23 10:08:18.000	1	53	2	5	30	0
40	2008-06-23 10:49:11.000	1	53	2	6	40	0
41	2008-06-23 10:54:45.000	1	53	2	7	57	0
42	2008-06-23 11:22:12.000	1	53	2	8	45	0
43	2008-06-23 11:23:55.000	1	53	2	9	50	0
46	2008-06-24 03:00:00.000	1	53	1	205	-2519.267803	0
49	2008-06-25 03:00:00.000	1	53	1	208	-34090.76025	0
15	2008-06-17 15:47:13.000	1	53	2	4	300	0
38	2008-06-23 09:14:58.000	1	53	2	4	100	0
44	2008-06-23 13:24:31.000	1	53	2	10	243679.47	0

Edited by - tkotey on 06/25/2008 08:18:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  08:23:07  Show Profile  Reply with Quote
What does Transtype designate?
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  08:40:26  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

What does Transtype designate?



TransType designate a credit/payment(2) or debit/invoice(1)

This is the result below when I run your statement


Index   CreateDate             UserIndex Total  Balance
3	2008-06-05 15:02:49.000	53	-288.2	-288.2
6	2008-06-10 08:59:10.000	53	1000	1000
7	2008-06-10 08:59:23.000	53	-13.2	-13.2
9	2008-06-12 11:42:46.000	53	-148.439793	-148.439793
11	2008-06-16 03:00:00.000	53	-304.598546	-304.598546
13	2008-06-17 03:00:00.000	53	-12040.136172	-12040.136172
18	2008-06-18 03:00:00.000	53	-39688.180278	-39688.180278
21	2008-06-19 03:00:00.000	53	-112987.200625	-112987.200625
24	2008-06-20 03:00:00.000	53	-14679.635	-14679.635
26	2008-06-20 18:20:12.000	53	60	60
28	2008-06-21 03:00:00.000	53	-55195.615937	-55195.615937
31	2008-06-22 03:00:00.000	53	-9790.15872	-9790.15872
34	2008-06-23 03:00:00.000	53	-254.1	-254.1
36	2008-06-23 08:11:15.000	53	78	78
39	2008-06-23 10:08:18.000	53	30	30
40	2008-06-23 10:49:11.000	53	40	40
41	2008-06-23 10:54:45.000	53	57	57
42	2008-06-23 11:22:12.000	53	45	45
43	2008-06-23 11:23:55.000	53	50	50
46	2008-06-24 03:00:00.000	53	-2519.267803	-2519.267803
49	2008-06-25 03:00:00.000	53	-34090.76025	-34090.76025
15	2008-06-17 15:47:13.000	53	300	300
38	2008-06-23 09:14:58.000	53	100	100
44	2008-06-23 13:24:31.000	53	243679.47	243679.47
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000