Setting WHERE on a VIEW with aggregate function.

[Bill Moseley <moseley@xxxxxxxx>]

I have a view to generate a list of instructors and a count of their
future classes.

"instructors" is a link table between "class" and "person".

CREATE VIEW future_instructor_counts
    AS
        SELECT  person.id AS person_id,
                first_name,
                last_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, last_name;


I have two very basic SQL questions:

1) With an aggregate function in the query, is there any way to remove
the "AND class_time > now()" so that timestamp can be passed in the
select?  That is, I'd like to be able to do this?

    select * from instructor_counts where class_time > now();

But class_time is not part of the VIEW so that's not valid.  And if it
was included then I don't have an aggregate function any more - no
more grouping.


2) I think I'm missing something obvious.  I know that I need to
specify all my non-aggregate columns in the "GROUP BY", but I don't
under stand why.  Really, the results are just grouped only by
person.id so why the need to specify the other columns.

And if you don't specify all the columns then Postgresql reports:

  ERROR:  column "person.id" must appear in the GROUP BY 
            clause or be used in an aggregate function

Is there a reason Postgresql doesn't just add the column
automatically?  It does in other cases (like a missing table in a
join).

Thanks


-- 
Bill Moseley
moseley@xxxxxxxx


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings