How to do addtion between two rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to do addtion between two rows?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-06 : 07:35:41

Hi

I have 4 rows in a table like below

orderId----- Prtype----Price------comType----debt
12345 ------ Pri-------90.00
12345 ------ shp-------20.00
12345 -----------------------------Shcom - 5.00
12345 -----------------------------Pricom - 10.00

How do I do additon for 'Price' column and 'debt' for the orderID '12345'. (i.e. 90.00 + 20.00 -5.00 -10.00).

I have more than 150 rows. Please help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 07:43:49

Select orderid,sum(coalesce(price,0)+coalesce(debt,0)) as total from table
group by orderid

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-06 : 08:02:42

Thanks a lot . It works

quote:
Originally posted by madhivanan

Select orderid,sum(coalesce(price,0)+coalesce(debt,0)) as total from table
group by orderid

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-06 : 08:25:25

try it out

select sum(coalesce(id,0))+sum(coalesce(id1,0)) from testing

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 08:28:42

quote:
Originally posted by arorarahul.0688

try it out

select sum(coalesce(id,0))+sum(coalesce(id1,0)) from testing

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

I am really curious to know, dont you read the replies before you reply?

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-06 : 10:01:51

Hi Madhivadhan

Your answer was very helpful. I am using only two row for addition and used your solution as below. It works fantastic.

SELECT OrderId, SUM(COALESCE (Price , 0)) AS total
FROM tbltest
GROUP BY OrderId

My Question: Is it possible to do the following

1. Divide the first row value by 1.175 and round up the result if the digit after the decimal is greater than 5 (ex. 22.99 / 1.175 = 19.5659 the result sould be 19.57 and same for the second row. finaly do SUM(COALESCE ...........

i.e OrderID----------Price
12345 -----------22.99
12345 ----------- 5.45

Expected result should be: 28.45 not 28.44

Sorry for hasle. Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-07 : 00:32:53

select cast(22.99/1.175 as decimal(12,2))

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-07 : 01:15:47

quote:
Originally posted by madhivanan

quote:
Originally posted by arorarahul.0688

try it out

select sum(coalesce(id,0))+sum(coalesce(id1,0)) from testing

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

I am really curious to know, dont you read the replies before you reply?

Madhivanan

Failing to plan is Planning to fail

yes till now i never read any reply
because it gives u an idea to think in a direction to sort the problem
and it will reduce your diversity to think about the answer what it can be ,or how it can be done ok bt i think now i should read answers before rply otherwise u will keep rpling
"u read rply b4 rply"

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-07 : 01:43:10

quote:
Originally posted by arorarahul.0688

quote:
Originally posted by madhivanan

quote:
Originally posted by arorarahul.0688

try it out

select sum(coalesce(id,0))+sum(coalesce(id1,0)) from testing

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

I am really curious to know, dont you read the replies before you reply?

Madhivanan

Failing to plan is Planning to fail

yes till now i never read any reply
because it gives u an idea to think in a direction to sort the problem
and it will reduce your diversity to think about the answer what it can be ,or how it can be done ok bt i think now i should read answers before rply otherwise u will keep rpling
"u read rply b4 rply"

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

You are welcome to post a reply if your suggestion is not already suggested by someone

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-07 : 02:01:56

quote:
Originally posted by madhivanan

quote:
Originally posted by arorarahul.0688

quote:
Originally posted by madhivanan

quote:
Originally posted by arorarahul.0688

try it out

select sum(coalesce(id,0))+sum(coalesce(id1,0)) from testing

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

I am really curious to know, dont you read the replies before you reply?

Madhivanan

Failing to plan is Planning to fail

yes till now i never read any reply
because it gives u an idea to think in a direction to sort the problem
and it will reduce your diversity to think about the answer what it can be ,or how it can be done ok bt i think now i should read answers before rply otherwise u will keep rpling
"u read rply b4 rply"

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

You are welcome to post a reply if your suggestion is not already suggested by someone

Madhivanan

Failing to plan is Planning to fail

i will take care now onwards
happyyyyy

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-11-07 : 06:47:57

Thank you very much. Very helpful

quote:
Originally posted by madhivanan

select cast(22.99/1.175 as decimal(12,2))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources