weekly,daily,yearly averages

in SQL

[shaan1980]

given say some data like

date salary
01-jan-94 90
01-feb-99 100

how do i get the weekly daily and yearly average of this data
in SQL please

[coopster]

Standard SQL uses date_time functions via an EXTRACT expression

YEAR: 
----- 
SELECT EXTRACT(YEAR FROM occurrence_timestamp) FROM table;

QUARTER: 
-------- 
SELECT EXTRACT(MONTH FROM occurrence_timestamp)/4 FROM table;

However some databases, like MySQL, have a WEEK function that comes in handy for queries such as these...

SELECT 
 YEAR(payday) AS Year, 
 WEEK(payday) AS Week, 
 AVG(amount) AS Avg 
 FROM salary 
 GROUP BY YEAR(payday), WEEK(payday) 
;

Daily:

SELECT 
 payday, 
 AVG(amount) AS Avg 
 FROM salary 
 GROUP BY payday 
;

Yearly:

SELECT 
 YEAR(payday) AS Year, 
 AVG(amount) AS Avg 
 FROM salary 
 GROUP BY YEAR(payday) 
;