GROUP BY requirement

On Fri, 2005-08-26 at 14:39, Bill Moseley wrote:

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?

OK, distinct on suffers from this problem. Given the following simple
dataset:

mytable:
a | b
------
1 | 0
1 | 1

select distinct on (a) a,b from mytable;

One can see how the possible results are:

1,0 and 1,1, right? All depending on the order in which they are
fetched.

The same would be true if you could do a group by on a and select b:

select a,b from mytable group by a;

Right?

Now, if it's impossible for your dataset to return such sets, due to the
way it's built, it is likely not fully normalized. I.e. you have data
like this:

classid | instructorname | moreinfo...
--------------------------------------
1 | 'John Smith' | 'information'
1 | 'John Smith' | 'even more information'

and so on. Or your join is creating such a data set.