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@hank.org