Weighted Averages on SQL 7 - 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 2000 Forums

Transact-SQL (2000)

Weighted Averages on SQL 7

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-08-30 : 09:49:12

I have a table called GammaAnalysis the Columns and datatypes are as follows:

BargeNumber char(10), SampleDateTime Smalldatetime, SampleTons decimal, BTUAsReceived float, BTUDryBasis float SulfurDioxide float, SulphurAsReceived float, SulphurDryBasis float.

I have to do a weighted Average on each of the last five fields, based on the SampleTons of each line, grouped by the BargeNumber. Each BargeNumber Group may have anywhere between, 850 and a 1350 records.

I would usually use Analysis Services, but this is SQL 7. There are in excess of 265,000 records, or I would use an excel Pivot Table.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-30 : 12:44:26

Here's an example using the first field. just add the other 4 and you are all set:


SELECT 
  bargenumber, SUM(BTUAsReceived * SampleTons) / SUM(SampleTons) as BTUAsReceived_Avg
FROM
  GammaAnalysis
GROUP BY
  bargenumber

don't worry about 265,000 records -- the key is how many bargenumbers are there? SQL will perform this calculation very quickly and return the data you need. Always try to have SQL calculate numbers like this, as opposed to bringing in 100,000s of rows into Excel or somewhere else.

- Jeff

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-08-30 : 14:09:03

Ooops, I guess I left out the tougher part. I had the query done the way you did it until I realized the folloing.
The sample tons number is a cumulitive field. Let's say the barge had 90 samples, the first Bargetons field would have 100, the next row would have 200 until finally the total tons put on the barge would be the last bargetons in the group.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-30 : 14:12:59

I think you need to give some sample data and results you expect. Why is that column cumulative? If it is, how are you defining your "weighted average" requirement? Weighted based on what?

i.e., please provide info like this so we can cut and paste into QA and run it and write your SQL for you:

CREATE TABLE blah blah

INSERT INTO TABLE VALUES ( .....)
INSERT INTO TABLE VALUES ( .....)
INSERT INTO TABLE VALUES ( .....)

Then clearly show what your expected resuts are.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources