Re: Why using "Group By"

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

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...

-- 
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/