Re: Why using "Group By"

"Damjan S. VujnoviÄ?" <damjan_at_NO_SPAM.galeb.etf.bg.ac.yu> wrote in message news:b4qke3$qnf$1_at_news.etf.bg.ac.yu...
> Tony wrote:
> >>How will you write (using your sintax) something like this:
> >>
> >>SELECT
> >> CUSTOMER_CITY, COUNT(*)
> >>FROM
> >> CUSTOMER_TABLE
> >>GROUP BY
> >> CUSTOMER_CITY, SOME_OTHER_CUSTOMER_TABLE_ATTRIBUTE
> >
> >
> > Well, why would you really want to? If I do that and the result is:
> >
> > CUSTOMER_CITY COUNT(*)
> > LONDON 12
> > LONDON 23
> > LONDON 34
> > PARIS 17
> > PARIS 99
> >
> > - what on Earth is the use of that "information"?
> >
> > This is another case of SQL bags rather than sets complicating queries.
>
> OK. Consider the following example. Students are taking exams from
> subjects. One student from one subject can take many exams. Suppose we
> have table exam:
>
> CREATE TABLE exam (
> exam_id INTEGER NOT NULL PRIMARY KEY,
> student_id INTEGER NOT NULL
> FOREIGN KEY REFERENCES student(student_id),
> subject_id INTEGER NOT NULL
> FOREIGN KEY REFERENCES subject(subject_id));
>
> The goal is: for each student, find the maximum times he took exam from
> any subject. In order not to use inline views, I'll use views
> explicitly, but it's easy to rewrite this snippet using inline views:
>
> CREATE VIEW s_t(student_id, times) AS
> SELECT student_id, COUNT(*)
> FROM exam
> GROUP BY student_id, subject_id;
>
> SELECT student_id, max(times) as max_times
> FROM s_t
> GROUP BY student_id
>
> Example. If the content of the exam table is:
>
> exam_id, student_id, subject_id
> 1, 1, 1
> 2, 1, 1
> 3, 1, 1
> 4, 1, 2
> 5, 1, 2
> 6, 2, 1
> 7, 2, 2
> 8, 2, 2
> 9, 3, 2
>
> The result should be:
>
> student_id, max_times
> 1, 3
> 2, 2
> 3, 1
>
> Because student 1 took exam [from subject 1] exactly 3 times, student 2
> took exam [from subject 2] exactly 2 times, student 3 took exam [from
> subject 2] exactly "1 times" (once).
>
> Now, took at the vies s_t. There is no need to select subject_id because
> we won't use it later...

For this one query. Omitting it makes the view useless for almost every other conceivable query. Why not include subject_id in the view? Doing so won't harm this query at all. Received on Thu Mar 13 2003 - 20:43:04 CET