Re: Why using "Group By"
Date: Thu, 13 Mar 2003 19:57:46 +0100
>>How will you write (using your sintax) something like this:
>> CUSTOMER_CITY, COUNT(*)
>> 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
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:
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...
-- Regards, Damjan S. Vujnovic University of Belgrade School of Electrical Engineering Department of Computer Engineering & Informatics Belgrade, Serbia http://galeb.etf.bg.ac.yu/~damjan/Received on Thu Mar 13 2003 - 19:57:46 CET