Help with a small Query - 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)

Help with a small Query

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-10-06 : 12:17:00

All,

I have Table ASH with the following field.

SaleDate(smalldatetime)
AggID (nvarchar)
ProdID (nvarchar)
Price (float)
cost (float)
units (bigInt)

The table represnts price & cost of a particular product, that belong to a particular AggID.

I have the following records.

SaleDate AggID ProdID Price Cost Units
2006-10-01 A123 123 1.90 1.70 10
2006-10-01 A123 456 1.90 1.70 10
2006-10-01 A123 789 1.80 1.65 10
2006-10-01 A999 999 2.80 2.25 10
2006-10-01 A999 888 2.70 2.20 20

Now I want to get the Price & Cost at AggID level.
The formula for that is

AggPrice = (sum(price*units)) / (sum(units))
AggCost = (sum(cost*units)) / (sum(units))

So the calculation for AggID= A123 would be:
AggPrice = ((1.90*10)+(1.90*10)+(1.80*15)) / (10+10+15)
AggPrice = 1.8571

AggCost = ((1.70*10)+(1.70*10)+(1.65*15)) / (10+10+15
AggCost = 1.6785

Now I want to have a query which can process this calulation & can show the results as Follows per AggID

AggID AggPrice AggCost
A123 1.8571 1.6785

Remember i may have more AggID's.

Can someone please help me on this. Any help would be appreciated.

Thanks a million

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-06 : 13:39:14

[code]
Select
AggID,
AggPrice = (sum(price*units)) / (sum(units)) ,
AggCost = (sum(cost*units)) / (sum(units))
from
ASH
group by
AggID
order by
AggID

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources