handle these type of calculations

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 2008 Forums

Transact-SQL (2008)

handle these type of calculations

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-01-05 : 06:53:18

What is best way to handle these type of calculations

col1*col2/(col3/col4)

if we use group by clause in query.

multiple options like

1.((col1*col2)+(colA*colB))/((col3/col4)+(colC/colD))
2.(col1*col2)/(col3/col4)+(colA*colB)/(colC/colD)

T.I.A

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-05 : 07:43:01

As per MSDN "Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions in SQL Server. One row is returned for each group. Aggregate functions in the SELECT clause <select> list provide information about each group instead of individual rows."

So if you needs group wise result then you will need to use the Group By clause. For example

Customer_id Amount Deduction
1 10.00 1.00
1 12.00 2.00
1 13.00 1.00

and you want to find the average amount for every cutomer_ID then you will need to do this via group by.

Select Customer_ID,Sum(Amount-Deduction)
From TableName
Group by Customer_ID

Will return like

Customer_Id
1 31.00

On the other hand if you need the output like

Customer_ID Amount Deduction NetAmount
1 10.00 1.00 9.00
1 12.00 2.00 10.00
1 13.00 1.00 12.00

Then you will need simple operation over columns

Select Customer_ID,Amount,Deduction,(Amount-Deduction) NetAmount From TableName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources