Re: Setting WHERE on a VIEW with aggregate function.

On Fri, Sep 16, 2005 at 12:06:19PM -0700, Roger Hand wrote:
> > select * from instructor_counts where class_time > now();
> >
> > But class_time is not part of the VIEW so that's not valid.
>
> No problem, just make it a part of the view. See the classes section below.
>
> CREATE VIEW future_instructor_counts
> AS
> SELECT * FROM
>
> (SELECT
> person.id AS person_id,
> first_name,
> last_name) personinfo
>
> INNER JOIN
>
> -- Add class_time field!
> (SELECT class.id, class_time FROM class
> WHERE class_time > now() ) classes
>
> INNER JOIN
>
> (SELECT
> id, count(class) AS class_count
> FROM instructors GROUP BY id) classcount
>
> ON personinfo.person_id = instructors.id
> AND classes.id = instructors.id

I couldn't get that to work -- Postgresql isn't that helpful just
reporting "ERROR: syntax error at or near ";" at character 496" even
after adding a FROM in the first select. So, I'm stabbing in the dark
to get it to work.

> [Disclaimer: I've not tested this code at all. It could help if you sent table definitions and maybe even dummy
> data via insert commands.]

Ok -- this should be cut-n-paste:

CREATE TABLE class (
id integer PRIMARY KEY,
class_time timestamp(0) with time zone,
name text
);

CREATE TABLE person (
id integer PRIMARY KEY,
first_name text
);

create table instructors (
person integer NOT NULL REFERENCES person,
class integer NOT NULL REFERENCES class,
PRIMARY KEY (person, class)
);

INSERT INTO person (id,first_name) values (1,'Joe');
INSERT INTO person (id,first_name) values (2,'Mary');
INSERT INTO person (id,first_name) values (3,'Bob');
INSERT INTO person (id,first_name) values (4,'Cindy');

INSERT INTO class (id,name, class_time) values (1,'Math', now());
INSERT INTO class (id,name, class_time) values (2,'Math', now() + interval '1 day');
INSERT INTO class (id,name, class_time) values (3,'Science', now());
INSERT INTO class (id,name, class_time) values (4,'PE', now() + interval '1 day');

INSERT INTO instructors (person, class) values (1,1); -- joe teaches math now

INSERT INTO instructors (person, class) values (1,2); -- joe teaches math tomorrow
INSERT INTO instructors (person, class) values (2,2); -- with Mary

INSERT INTO instructors (person, class) values (3,3); -- Bob teaches science now
INSERT INTO instructors (person, class) values (4,3); -- Cindy teaches science tomorrow

-- view

CREATE VIEW instructor_counts
AS
SELECT person.id AS person_id,
first_name,
count(instructors.class) AS class_count

FROM class, instructors, person

WHERE class.id = instructors.class AND
person.id = instructors.person
-- AND class_time > now()

GROUP BY person_id, first_name;

select * from instructor_counts order by class_count desc;

-- Returns:

person_id | first_name | class_count
-----------+------------+-------------
1 | Joe | 2
2 | Mary | 1
3 | Bob | 1
4 | Cindy | 1
(4 rows)

My GOAL above is to be able to add a WHERE class_time > $some_time.

Here's were I left off, which I never could get to work.
The individual selects work, but seems like I need to be say
c.class_id = i.class in addition. But I can't even get
this without syntax errors:

CREATE VIEW instructor_counts
AS
SELECT *

FROM

(SELECT person.id AS person_id, first_name
FROM person) p

INNER JOIN

(SELECT class.id AS class_id, class_time
FROM class) c

INNER JOIN

(SELECT person, count(class) AS class_count
FROM instructors GROUP BY person) i

ON ( p.person_id = i.person);

That also looks like the selects are going to be full table scans.

--
Bill Moseley
moseley(at)hank(dot)org