HELP in Calculation - 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)

HELP in Calculation

Author

Topic

mike1973
Starting Member

46 Posts

Posted - 2010-04-20 : 10:18:39

hello,

Please i need your help guys in getting the average column for Distinct Categories:

Here is my query:

Select DISTINCT(categoryid), CategoryAR, SUM(Weight) AS TotalWeight, (UnitPrice*Weight) AS TotalPrice
from DailyOperations
JOIN Categories ON Categories.CategoryAutoID=DailyOperations.CategoryID
Where
DateOperated BETWEEN CONVERT(DATETIME,@StartDate,101) + ' 12:00:00 AM' AND CONVERT(DATETIME,@EndDate,101) + '11:59:59 PM'
GROUP BY categoryid, CategoryAR,UnitPrice,Weight

I need to get for Each Category in the DailyOperations Table, the following Records
CategoryID, CategoryAR, Total Weight, Total Price, Average (TotalPrice/TotalWeight)

Can you please help?

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 10:22:33

[code]Select do.categoryid, c.CategoryAR,do.TotalWeight,do.TotalPrice, AVG(do.TotalPrice*1.0/do.TotalWeight)
from
(SELECT CategoryID,SUM(Weight) AS TotalWeight, SUM(UnitPrice*Weight) AS TotalPrice
FROM DailyOperations
Where
DateOperated BETWEEN CONVERT(DATETIME,@StartDate,101) + ' 12:00:00 AM' AND CONVERT(DATETIME,@EndDate,101) + '11:59:59 PM'
GROUP BY CategoryID)do
JOIN Categories c ON c.CategoryAutoID=do.CategoryID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources