Average of rows (needs grouping)

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

General SQL Server Forums

New to SQL Server Programming

Average of rows (needs grouping)

Author

Topic

vpillai
Starting Member

3 Posts

Posted - 2012-03-13 : 11:21:35

Hello,

I'm trying to write a SQL code for a program that uses SQL. I'm trying to calculate the average of the recovery lengths and the average of the depths for 5 runs (R-1 to R-5, R-6 to R-10 etc) at a time (without weighting it). For example, I need the average of the recovery lengths and depth corresponding to Run numbers R-1 to R-5. I've included an image below.

The table is called SAMPLE. I would like to display only the averages and I want to do this without adding additional columns to the table.

Please let me know. Any kind of help would be appreciated. Thanks.
Vinod

vpillai
Starting Member

3 Posts

Posted - 2012-03-13 : 12:42:27

Is it possible to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 15:03:23

you mean this?


SELECT Type,(Rn-1)%5 AS Batch,AVG(Depth) AS AvgDepth,AVG(RecoveryLEngth) AS AvgRecoveryLength
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Number) AS Rn,*
FROM table
)t
GROUP BY Type,(Rn-1)%5

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources