Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

chris=# create table marks (name varchar(30), mark int);
CREATE TABLE
chris=# insert into marks values ('Chris', 18);
INSERT 0 1
chris=# insert into marks values ('Chris', 23);
INSERT 0 1
chris=# insert into marks values ('Anna', 30);
INSERT 0 1
chris=# insert into marks values ('Andreas', 18);
INSERT 0 1
chris=# insert into marks values ('Andreas', 24);
INSERT 0 1
chris=# select * from marks;
  name   | mark 
---------+------
 Chris   |   18
 Chris   |   23
 Anna    |   30
 Andreas |   18
 Andreas |   24
(5 rows)

chris=# select name, avg(mark) from marks group by name;
  name   |         avg         
---------+---------------------
 Andreas | 21.0000000000000000
 Anna    | 30.0000000000000000
 Chris   | 20.5000000000000000
(3 rows)

chris=# create table tmarks (name varchar(30), mark int, f date, t date);
CREATE TABLE
chris=# insert into tmarks values ('Chris', 18, '2009-01-01', '2009-01-20');
INSERT 0 1
chris=# insert into tmarks values ('Chris', 19, '2009-01-20', null);
INSERT 0 1
chris=# insert into tmarks values ('Chris', 30, '2009-01-01', null);
INSERT 0 1
chris=# select * from tmarks;
 name  | mark |     f      |     t      
-------+------+------------+------------
 Chris |   18 | 2009-01-01 | 2009-01-20
 Chris |   19 | 2009-01-20 | 
 Chris |   30 | 2009-01-01 | 
(3 rows)

chris=# select name, avg(mark) from tmarks where '2009-02-01' between f and coalesce(t, '2099-01-01') group by name;
 name  |         avg         
-------+---------------------
 Chris | 24.5000000000000000
(1 row)

chris=# select name, avg(mark) from tmarks where '2009-01-10' between f and coalesce(t, '2099-01-01') group by name;
 name  |         avg         
-------+---------------------
 Chris | 24.0000000000000000
(1 row)