Average on the same row by excluding 1st 3 rows

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 2008 Forums

Transact-SQL (2008)

Average on the same row by excluding 1st 3 rows

Author

Topic

pavan2020
Starting Member

5 Posts

Posted - 2011-03-03 : 14:37:25

I have an excel sheet that actually does the avarage on above 3 rows and displays in 4th row and followed entire Column. That means in the Formula row (for the first time it will have the row 1,2 and row 3) is empty and does the Average. The same i tried in SQL to display the Avarage on the same row... However I am not able to show the results on the same row...But the result are comming in 1st row instead of 4th row.

From Excel I have the Formula is =SUM(B2:B4)/3

I am doing avarage for B Column on G Column

For the same i have writen the SQL as below

  WITH RankedPrices
AS
(SELECT    i_serial , I_Open, ROW_NUMBER() OVER (ORDER BY i_serial) AS rn
FROM         IC_Raw_In)
    SELECT    a.i_serial, AVG(b.I_Open) AS AvgPrice
     FROM         RankedPrices AS a INNER JOIN
                            RankedPrices AS b ON b.rn BETWEEN a.rn AND a.rn + 2
  GROUP BY a.i_serial

---IC_Raw_In table has B column data

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-03 : 21:23:18

[code]WITH RankedPrices
AS
(SELECT i_serial , I_Open, ROW_NUMBER() OVER (ORDER BY i_serial) AS rn
FROM IC_Raw_In)
SELECT a.i_serial, AVG(b.I_Open) AS AvgPrice
FROM RankedPrices AS a ~~INNER~~ LEFT JOIN
RankedPrices AS b ON b.rn BETWEEN a.rn-2 AND a.rn ~~+ 2~~
and b.rn >= 3 -- if you want the first two to be null
GROUP BY a.i_serial[/code]

pavan2020
Starting Member

5 Posts

Posted - 2011-03-04 : 08:43:19

A Special & many Thanks to Sunitabeck.

It Worked for me... You made my life easy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-04 : 11:29:07

You are very welcome; glad it worked out for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources