CODEWITHSUNDEEP
sql-serverdatasetpivotaverage
Oscar Duran
Oscar Duran

Reputation: 117

SQL add a row and column at the end of the table to display average

I am provided with the following dataset:

ID   STATION ID   DATE         ELEMENT ID   00     01     02     03......23
1    6106000      2009-05-11   110          2550   900    1050   1550    3000
2    6106000      2009-05-12   110          380    1400   1550   5090    200
3    6106000      2009-05-13   110          500    1250   800    3550    4020

From the dataset, I have computed probabilities for certain days of past years. This is what I have wrote in SQL:

SELECT * FROM
(SELECT 
DatePart(year, date) AS 'Date',
Format([DATE],'MMM dd') AS 'Day',
Probability = Cast(Round(((
IIf([00]>1410,1,IIf([00]=0,1,0)) + 
IIf([01]>1410,1,IIf([01]=0,1,0)) + 
IIf([02]>1410,1,IIf([02]=0,1,0)) + 
IIf([03]>1410,1,IIf([03]=0,1,0)) + 
IIf([04]>1410,1,IIf([04]=0,1,0)) + 
IIf([05]>1410,1,IIf([05]=0,1,0)) + 
IIf([06]>1410,1,IIf([06]=0,1,0)) + 
IIf([07]>1410,1,IIf([07]=0,1,0)) + 
IIf([08]>1410,1,IIf([08]=0,1,0)) + 
IIf([09]>1410,1,IIf([09]=0,1,0)) + 
IIf([10]>1410,1,IIf([10]=0,1,0)) + 
IIf([11]>1410,1,IIf([11]=0,1,0)) + 
IIf([12]>1410,1,IIf([12]=0,1,0)) + 
IIf([13]>1410,1,IIf([13]=0,1,0)) + 
IIf([14]>1410,1,IIf([14]=0,1,0)) + 
IIf([15]>1410,1,IIf([15]=0,1,0)) + 
IIf([16]>1410,1,IIf([16]=0,1,0)) + 
IIf([17]>1410,1,IIf([17]=0,1,0)) + 
IIf([18]>1410,1,IIf([18]=0,1,0)) + 
IIf([19]>1410,1,IIf([19]=0,1,0)) + 
IIf([20]>1410,1,IIf([20]=0,1,0)) + 
IIf([21]>1410,1,IIf([21]=0,1,0)) + 
IIf([22]>1410,1,IIf([22]=0,1,0)) + 
IIf([23]>1410,1,IIf([23]=0,1,0)))/24.0)*100.0,0) AS int)
FROM 
ON_2
WHERE 
((([ELEMENT ID])=110) AND 
(([STATION ID])='6106000') AND 
((Day([DATE])) BETWEEN 1 AND 15) AND 
((DatePart("m",[DATE]))=12))) AS BaseData
PIVOT 
(MAX([Probability]) 
FOR [DATE] 
IN ([2000],[2001],[2002],[2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010],[2011])) AS PivotTable

After running the query, the result is as follows:

Day     2000  2001  2002  2003....2011
Dec 01  100   19    20    68......95
Dec 02  56    33    80    77......35
Dec 03  90    52    39    10......45
.
.
Dec 15  58    43    70    25......99

My issue here is that I am not able to add a column and a row to show averages. This is what I am trying to get as a final result:

Day     2000  2001  2002  2003....2011  Avg
Dec 01  100   19    20    68......95    60
Dec 02  56    33    80    77......35    88
Dec 03  90    52    39    10......45    48
.
.
Dec 15  58    43    70    25......99    55
Avg     75    40    55    33......58

Thanks for your time and help beforehand.

Upvotes: 2

Views: 1251

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

Just sum all the columns and divide it by number of columns you want to sum. Something like this.

;with cte as
(
select * from result
)
SELECT isnull(Day,'Avg'),
       Avg([2000]) [2000],
       Avg([2001]) [2001],
       Avg([2002]) [2002],
       Avg([2003]) [2003],
       Avg([2011]) [2011],
       ( Avg([2000]) + Avg([2001]) + Avg([2002])
         + Avg([2003]) + Avg([2011]) ) / 5 as [AVG]
FROM   cte
GROUP  BY day WITH rollup

SQLFIDDLE DEMO

Upvotes: 2

Related Questions