select *
  from (select max([S.No.]) as [S.No.]
              ,cast(round(avg(Marks*1.0),0) as int) as AvgMarks
          from Marks
         group by ([S.No.]-1)/3
       ) as a
 where [S.No.]%3=0

quote:

---

Originally posted by Ajoo

Hi Gurus,

Back again, I tried using visakh's solution but i get the error "#1305 - FUNCTION ROW_NUMBER does not exist". I must admit I find Visakh's code very new cos I have not seen these sort of instructions in mysql. Maybe if Visakh / any gure can guide me some, i'll be able to execute the pieces of code as listed by Visakh.

The second bit of code also gave the following error : "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY SNo ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS AvgVal, ROW_NUMBER()' at line 4".
Thanks guys. Looking forward more replies on this soon. Thanks loads.

---

select *
from (select max(sno) as sno
,cast(round(avg(Marks*1.0),0) as unsigned) as AvgMarks
from Marks
group by (sno-1)/3 
) as a
where sno%3=0 

quote:

---

Originally posted by Ajoo

Hi all ! Visakh that's the 100% exactly copy of what i wrote in my post 2 replies ago. This one executes but fails to Average in groups of 3 and gives only the value at positions like at row 3, 6, 9 & 12. Kindly look into it. Here is actual run of the code you just gave on the Marks table modified below:

Marks Table
sno Marks
1 5
2 5
3 6
4 6
5 7
6 6
7 7
8 6
9 8
10 7
11 7
12 8
13 9
14 10
15 11

The Result :

sno AvgMarks
3 6
6 6
9 8
12 8
15 11

As can be seen, there is no average but the actual marks value at those ( 3rd, 6th, 9th ..rows) positions.
Kindly help solve this. Thanks all.

---

select *
  from (select max(sno) as sno
              ,round(avg(Marks),0) as AvgMarks
          from Marks
         group by floor((sno-1)/3)
       ) as a
 where sno%3=0

quote:

---

Originally posted by Ajoo

Hey ! wow ! worked like a charm. Thanks loads. Gurus are great !! Just one more request if you can explain exactly how it works, this query. I have been trying to disect it and see but still cannot completely get it. How, for eg., is it able to pick the 3 consecutive values and average them. Maybe if you create an intermediate field on which it is getting grouped, I would be able to understand it better. I know what floor(sno-1)/3 AND sno%3 = 0 do but the logic is unclear. Kindly explain. Thanks again.

---

sno mark groupvalue
  1    5 (1-1)/3=0
  2    5 (2-1)/3=0
  3    6 (3-1)/3=0
  4    6 (4-1)/3=1
  5    7 (5-1)/3=1
  6    6 (6-1)/3=1
  7    7 (7-1)/3=2
  8    6 (8-1)/3=2
  9    8 (9-1)/3=2
 10    7 (10-1)/3=3
 11    7 (11-1)/3=3
 12    8 (12-1)/3=3
 13    9 (13-1)/3=4
 14   10 (14-1)/3=4
 15   11 (15-1)/3=4

maxsno avgmark
     3 (5+5+6)/3=5
     6 (6+7+6)/3=6
     9 (7+6+8)/3=7
    12 (7+7+8)/3=7
    15 (9+10+11)/3=10

maxsno avgmark
     3 (5+5+6)/3=5
     6 (6+7+6)/3=6
     9 (7+6+8)/3=7
    12 (7+7+8)/3=7
    14 (9+10)/3=9