AVG CALC - 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

General SQL Server Forums

New to SQL Server Programming

AVG CALC

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2006-02-10 : 20:33:09

New SQL - but certainly enjoying the experience.

I need to calculation an Average in SQL -

A view I created contains:

Item WorkOrders TotalProduced
A 10 125
B 1 10
C 3 48

Desired Results
Item AVGPRODUCED
A 12.5
B 10
C 16

The view will be used by a Crystal Report so I could calculate in Crystal - But I was wondering how to in SQL

Any help would be appreciated

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-10 : 22:54:22

Select Item, (WorkOrders/TotalProduced) as AVGPRODUCED fromUrTable

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-10 : 22:55:01

[code]select Item, (TotalProduced * 1.0 / WorkOrders) as AvgProduced
from yourtable[/code]

Note : the multiply by 1.0 is to convert value from int to numbers with decimal places before the division else the result will be 12 instead of 12.5

----------------------------------
'KH'
everything that has a beginning has an end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-10 : 22:56:14

quote:
Originally posted by Srinika

Select Item, (WorkOrders/TotalProduced) as AVGPRODUCED fromUrTable

Srinika, you got the division the other way round

----------------------------------
'KH'
everything that has a beginning has an end

btamulis
Yak Posting Veteran

64 Posts

Posted - 2006-02-11 : 04:21:00

Thanks - Learn something new everyday......

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-11 : 16:17:04

quote:
Originally posted by khtan

quote:
Originally posted by Srinika

Select Item, (WorkOrders/TotalProduced) as AVGPRODUCED fromUrTable

Srinika, you got the division the other way round

----------------------------------
'KH'
everything that has a beginning has an end

My Appologies

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 00:54:06

It is because of implicit conversion of integer data type

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources