July 16, 2015 at 2:16 pm
I have a table like this
Name Cost Year Month
shory 34346 2014 1
qualig 4637 2015 1
qualig 6044 2014 10
shory 45455 2014 11
shory 4738 2014 12
shory 24552 2014 2
Now I want to calculate the relative average, meaning I go to Jan 2014, take the cost and divide it by average(cost in jan14+ feb14+march14).Also if have more than one value for cost of a company in 1 2014, i would want to sum it up and then calculate the relative average.
now for the company qualig for 10/2014 i want 6044/average(4637+6044). for the company qualig for 1/2014 I want 4637/average((4637+6044)
July 16, 2015 at 2:19 pm
s.raddy.89 (7/16/2015)
Hi I have a table like thisNameCostMonthYear
Dgjsdj384738740114
Dgjsdj3084895214
Dgjsdj64376482314
Now I want to calculate the relative average, meaning I go to Jan 2014, take the cost and divide it by average(cost in jan14+ feb14+march14)
How do i do this?
Please help
What about February and March?
July 16, 2015 at 2:52 pm
Yes I need to calculate the relative average for all months~!
July 16, 2015 at 2:52 pm
Lynn Pettis (7/16/2015)
s.raddy.89 (7/16/2015)
Hi I have a table like thisNameCostMonthYear
Dgjsdj384738740114
Dgjsdj3084895214
Dgjsdj64376482314
Now I want to calculate the relative average, meaning I go to Jan 2014, take the cost and divide it by average(cost in jan14+ feb14+march14)
How do i do this?
Please help
What about February and March?
Also, how does the name column figure into this? Your sample data only shows one value for name. What happens if name is different? Should both values be combined into the same sum? Different sums?
I think what you're asking for is fairly simple, but there are too many unknowns for us to provide a answer.
Please provide better explanation of what you need and any conditions that apply.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 16, 2015 at 2:54 pm
s.raddy.89 (7/16/2015)
Yes I need to calculate the relative average for all months~!
For all years? Average over each year?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 16, 2015 at 3:23 pm
It sounds like what you are looking for is the following.
SELECT name, cost, yr, mth, CAST(cost/AVG(cost) OVER(PARTITION BY name) AS DECIMAL(10,4))
FROM (
VALUES
('shory', 34346.00, 2014, 1)
,('qualig', 4637, 2015, 1)
,('qualig', 6044, 2014, 10)
,('shory', 45455, 2014, 11)
,('shory', 4738, 2014, 12)
,('shory', 24552, 2014, 2)
) AS tbl(name, cost, yr, mth)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 17, 2015 at 8:57 am
s.raddy.89 (7/16/2015)
Yes I need to calculate the relative average for all months~!
I was actually looking for the formula you wanted for each of those months. In fact, how should the calculation be done if you are returning a full year of data? From January to December of the same year? from July of one year to June of the next year?
You really didn't provide enough information to provide an appropriate answer except maybe for the limited information you did provide.
July 21, 2015 at 10:44 am
s.raddy.89 (7/16/2015)
I have a table like thisName Cost Year Month
shory 34346 2014 1
qualig 4637 2015 1
qualig 6044 2014 10
shory 45455 2014 11
shory 4738 2014 12
shory 24552 2014 2
Now I want to calculate the relative average, meaning I go to Jan 2014, take the cost and divide it by average(cost in jan14+ feb14+march14).Also if have more than one value for cost of a company in 1 2014, i would want to sum it up and then calculate the relative average.
now for the company qualig for 10/2014 i want 6044/average(4637+6044). for the company qualig for 1/2014 I want 4637/average((4637+6044)
After thinking about this, I think I may have the solution, but you'll have to let me know if it works correctly for all scenarios. I assumed you wanted exactly what you said, and that the average is a value by company, after first summing the value for each given company, year, and month. I then took the monthly values for each company, year, and month and divided them by the company monthly average:
--Name Cost Year Month
DECLARE @DATA AS TABLE (
CompanyName varchar(20),
Cost decimal(15,2),
[Year] int,
[Month] tinyint
)
INSERT INTO @DATA
VALUES
('shory', 34346, 2014, 1),
('qualig', 4637, 2015, 1),
('qualig', 6044, 2014, 10),
('shory', 45455, 2014, 11),
('shory', 4738, 2014, 12),
('shory', 24552, 2014, 2);
WITH GROUPED_DATA AS (
SELECT D.CompanyName, D.[Year], D.[Month], SUM(D.Cost) AS MonthlyCost
FROM @DATA AS D
GROUP BY D.CompanyName, D.[Year], D.[Month]
)
SELECT GD.CompanyName, GD.[Year], GD.[Month], D.Cost,
AVG(GD.MonthlyCost) OVER(PARTITION BY GD.CompanyName) AS AverageCost,
SUM(GD.MonthlyCost) OVER(PARTITION BY GD.CompanyName) AS TotalCost,
D.Cost / AVG(GD.MonthlyCost) OVER(PARTITION BY GD.CompanyName) AS RelativeAverage
FROM GROUPED_DATA AS GD
INNER JOIN @DATA AS D
ON GD.CompanyName = D.CompanyName
AND GD.[Year] = D.[Year]
AND GD.[Month] = D.[Month]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 21, 2015 at 11:46 am
As far as I can tell, this yields exactly the same plan, but I find it simpler to follow.
SELECT CompanyName, Year, Month, Cost
,AVG(Cost) OVER(PARTITION BY CompanyName) AS AverageCost
,SUM(Cost) OVER(PARTITION BY CompanyName)
,AVG(Cost) OVER(PARTITION BY CompanyName, Year, Month) / AVG(Cost) OVER(PARTITION BY CompanyName)
FROM @DATA
ORDER BY CompanyName, Year, MONTH
In my previous post, I assumed that the data provided was already summarized by month, but this is a more general approach.
Drew
PS: It appears that the OP hasn't logged in in awhile, so we may never get an answer to some of these questions.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 21, 2015 at 7:47 pm
drew.allen (7/21/2015)
As far as I can tell, this yields exactly the same plan, but I find it simpler to follow.
SELECT CompanyName, Year, Month, Cost
,AVG(Cost) OVER(PARTITION BY CompanyName) AS AverageCost
,SUM(Cost) OVER(PARTITION BY CompanyName)
,AVG(Cost) OVER(PARTITION BY CompanyName, Year, Month) / AVG(Cost) OVER(PARTITION BY CompanyName)
FROM @DATA
ORDER BY CompanyName, Year, MONTH
In my previous post, I assumed that the data provided was already summarized by month, but this is a more general approach.
Drew
PS: It appears that the OP hasn't logged in in awhile, so we may never get an answer to some of these questions.
The O.P. did specifically state that the data needed to be summed for multiple observations for the same company, year, and month, so that's why I used the CTE to get the SUM first. I do agree, given the O.P.'s lack of response to date, we are unlikely to hear back...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply