July 31, 2008 at 4:31 am
Hi there,
I have a table which holds data over a time like this
AreaId 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002
345 23 26 67 86 98 45 47 78 78 54
456 54 36 54 45 65 56 87 67 76 86
678 45 24 34 45 64 33 34 45 78 85
890 23 52 43 24 52 64 64 78 86 56
789 34 53 67 75 74 32 35 74 67 94
How do I calculate the average for Areas across time, like 60,2 for AreaId 345 or 54,2 for AreaID 456?
And how do I caluculate the overall average for all the numbers, which is 57,24?
Best Regards Joejoe
July 31, 2008 at 7:38 am
i'm not saying this is the best way, but this is a good starting point. basically, this is treating your original table as a pivot, then it's just unpivoting and running averages.
/*
-- create the table with the values
create table #TempAvg (AreaID int null, [1993] int null,[1994] int null,[1995] int null,[1996] int null,[1997] int null,[1998] int null, [1999] int null, [2000] int null,[2001] int null,[2002] int null)
insert into #TempAvg Values (345, 23, 26, 67, 86, 98, 45, 47, 78,78,54)
go
insert into #TempAvg Values (456, 54, 36, 54, 45, 65, 56, 87, 67, 76, 86)
go
insert into #TempAvg Values (678, 45, 24, 34, 45, 64, 33, 34, 45, 78, 85)
go
insert into #TempAvg Values (890, 23, 52, 43, 24, 52, 64, 64, 78, 86, 56)
go
insert into #TempAvg Values (789, 34, 53, 67, 75, 74, 32, 35, 74, 67, 94)
go
*/
-- show the values
select*
from#TempAvg
-- create a holding table
CREATE TABLE #TempUnPivotAvg (AreaID INT NULL, YearNum INT NULL, Total DECIMAL(4,2) NULL)
-- unpivot the original table to get 3 columns: areaid, year and total
INSERT INTO #TempUnPivotAvg
SELECTAreaID, YearNum, Total
FROM(
SELECTAreaID, [1993],[1994],[1995],[1996],[1997],[1998],[1999],[2000],[2001],[2002]
FROM#TempAvg
) TempPivot
UNPIVOT
(
Total FOR YearNum IN ([1993],[1994],[1995],[1996],[1997],[1998],[1999],[2000],[2001],[2002])
) AS TempUnPivot
-- show averages
SELECTCAST(AVG(Total) AS DECIMAL(3,1))
FROM#TempUnPivotAvg
WHEREAreaID = 345
SELECTCAST(AVG(Total) AS DECIMAL(4,2))
FROM#TempUnPivotAvg
DROP TABLE #TempUnPivotAvg
if you add new rows to your original table, you'll have to modify the unpivot to allow for that. but now you can calc averages by areaid/year, you can do sums, etc.
hope this helps...
lenny
August 1, 2008 at 1:15 pm
Hi Lenny,
This is very elegant, and exactly what I needed. I had something like cursor & loops in my mind, but this is much more handy.
Thanks alot & have a nice weekend
Joejoe
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply