CODEWITHSUNDEEP
mysqlsqlgroup-byado
mllamazares
mllamazares

Reputation: 8196

How to GROUP BY in groups of N rows?

I have a table called PEOPLE with the following data:

MYNAME    AGE     MYDATE
==========================
MARIO     20      2015/02/03
MARIA     10      2015/02/02
PEDRO     40      2015/02/01
JUAN      15      2015/01/03
PEPE      20      2015/01/02
JULIA     30      2015/01/01
JUANI     50      2014/02/03
MARTIN    10      2014/02/03
NASH      21      2014/02/03

Then I want to get the average of age grouping the people in groups of 3 ordering by MYDATE descending. I mean, the result that I'm looking for would be something like:

23,3 
21,6 
27

Where 23,3 is the average of the age of:

MARIO   20      2015/02/03
MARIA   10      2015/02/02
PEDRO   40      2015/02/01

And 21,6 is the average of the age of:

JUAN    15      2015/01/03
PEPE    20      2015/01/02
JULIA   30      2015/01/01

And 27 is the average of the age of:

JUANI   50      2014/02/03
MARTIN  10      2014/02/03
NASH    21      2014/02/03

How could I handle this? I know how to use GROUP BY but only to group for a particular field of the table.

Upvotes: 0

Views: 74

Answers (2)

Ewan
Ewan

Reputation: 1087

Try - You can also then use Grp to get which three people the average relates to

select MYNAME, AGE, MYDATE, RN / 3 As Grp into #x
from
(select MYNAME, AGE , MYDATE, ROW_NUMBER() over(order by MyDate) + 2 as RN
from MYdata)x

select Grp, AVG(Age) as AvgAge
From #x
Group By Grp

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270371

SQL tables are inherently unordered, so I assume that you want to order by mydate descending. You can enumerate the rows using variables, use arithmetic to define the groups, and then get the average:

select avg(age)
from (select t.*, (@rn := @rn + 1) as seqnum
      from table t cross join
           (select @rn := 0) vars
      order by mydate desc
     ) t
group by floor((seqnum - 1) / 3);

Upvotes: 4

Related Questions