GROUP BY requirement

From: Bill Moseley <moseley(at)hank(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: GROUP BY requirement
Date: 2005-08-26 19:39:37
Message-ID: 20050826193937.GA17416@hank.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)hank(dot)org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2005-08-26 19:48:30 Re: PQConnectdb SSL (sslmode): Is this a bug
Previous Message Steve Manes 2005-08-26 19:23:45 PG 8.0 "CONNECT BY" patch