SUM two fileds from different rows from the same t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SUM two fileds from different rows from the same t

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 04:31:32

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 04:40:10

[code]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
[/code]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-06-25 : 04:44:40

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 04:51:06

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?

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 04:59:54

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 05:12:52

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

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 05:32:16

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 05:41:41

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

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 05:55:29

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 05:57:51

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.

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 06:10:41

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 06:14:52

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.

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 06:23:51

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 06:34:02

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.

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 07:30:58

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 07:46:43

[code]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[/code]

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 08:00:20

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 08:13:24

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.

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 08:16:44

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 08:23:07

What does Transtype designate?

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-06-25 : 08:40:26

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources