Seperating out SUM values - SQL Server Forums

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)

Seperating out SUM values

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2009-08-24 : 10:23:20

Imagine a data set that looks something like this:


ID     orderID     commission     value
1      1           15             110  
2      1           15             50
3      1           20             160
4      2           10             150
5      2           15             100
6      2           15             90
7      2           20             80

Currently I have a query that fetches back a bunch of other data across several tables, and includes a SUM() of value for all orders with the same orderID. So from the sample data I've given, order 1 would have a SUM() of 320 and order 2 would have a SUM() of 420.

I need to change this so that I can return an additional value which is the SUM of the values for a total order, but each individual value needs to be modified (reduced) by the commission as a percentage. So order1 should return 264.

I'm not sure how I can go about making the calculation on a row-by-row basis but then feed back a grouped total.

Help much appreciated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-24 : 10:27:44

Can you show us how your expected output should look like? Also provide the details of the calculation of how 320 becomes 264...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-24 : 10:28:25

select orderid,sum(value-value*commision/100.0) from table
group by orderid

Madhivanan

Failing to plan is Planning to fail

mattt
Posting Yak Master

194 Posts

Posted - 2009-08-24 : 10:36:46

quote:
Originally posted by madhivanan

select orderid,sum(value-value*commision/100.0) from table
group by orderid

Madhivanan

Failing to plan is Planning to fail

Thanks a lot. As ever I failed to appreciate that the answer was that straightforward. I always expect SQL to be more complicated than it often is :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-24 : 10:44:22

quote:
Originally posted by mattt

quote:
Originally posted by madhivanan

select orderid,sum(value-value*commision/100.0) from table
group by orderid

Madhivanan

Failing to plan is Planning to fail

Thanks a lot. As ever I failed to appreciate that the answer was that straightforward. I always expect SQL to be more complicated than it often is :)

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources