I'm wondering if adding a GROUP BY (as required by Postgres) will
change the results of a select on a view.
I have the following view which joins a "class" with a teacher. A
teacher is a "person" and I have an "instructors" link table.
CREATE VIEW class_list (id, class_time, instructor )
AS
SELECT DISTINCT ON(class.id)
class.id, class.class_time, person.first_name
FROM class, instructors, person
WHERE instructors.person = person.id
AND class.id = instructors.class;
I also have a table "registration" that links students with a class.
The registration table has a "reg_status" column to say if they are
confirmed or on the wait_list. So when showing the above I'd also
like to see how many students are confirmed and on the wait_list.
DROP VIEW cl;
CREATE VIEW cl (id, class_time, instructor,
confirmed_cnt, wait_list_cnt)
AS
SELECT DISTINCT ON(class.id)
class.id, class.class_time, person.first_name,
sum (CASE WHEN registration.reg_status = 1 THEN 1 ELSE 0 END) as confirmed_cnt,
sum (CASE WHEN registration.reg_status = 2 THEN 1 ELSE 0 END) as wait_list_cnt,
FROM class, instructors, person, registration
WHERE instructors.person = person.id
AND class.id = instructors.class
AND class.id = registration.class
GROUP BY class.id, class.class_time, person.first_name;
PostgreSQL requires the GROUP BY. But, I'm not clear how the GROUP BY
might change the results between the two views above.
http://www.postgresql.org/docs/8.0/static/sql-select.html#SQL-GROUPBY
says:
When GROUP BY is present, it is not valid for the SELECT list
expressions to refer to ungrouped columns except within aggregate
functions, since there would be more than one possible value to
return for an ungrouped column.
Frankly, I cannot see how it might change results of a select between
the two views. Am I missing something?
--
Bill Moseley
moseley@xxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
