Group records on quarterly basis

Hi All,
I have the fallowing columns
1.Empl id, name, score, date etc.
now i wanna find the avergae of score of an employee for each quater.I am using mysql server(i hope its obvious anyway)
How do i do that? can anybody help me.
ur help is greatly appreciated.
chinna

Hi,
If you are using SQL Server 2005, it is easy by using the ROW_NUMBER OVER PARTITION syntax like the script below:

select
table01.cust_id,
table01.cust_name,
t.disposition,
t.date
from table01
left join (
select
row_number() over (partition by cust_id order by cust_id) rownum,
cust_id,
disposition,
date
from table02
) t on t.cust_id = table01.cust_id and t.rownum = 1


I hope it helps,

Eralper
http://www.kodyaz.com

Hi,

You can use the below script for SQL2k



select
table01.cust_id,
table01.cust_name,
table02.disposition,
table02.date
from table01
left join (
select
cust_id,
max(date) date
from table02
group by cust_id
) t on t.cust_id = table01.cust_id
left join table02 on t.cust_id = table02.cust_id and t.date = table02.date

Eralper
http://www.kodyaz.com