Re: Why using "Group By"

From: Damjan S. Vujnović <damjan_at_NO_SPAM.galeb.etf.bg.ac.yu>
Date: Thu, 13 Mar 2003 19:57:46 +0100
Message-ID: <b4qke3$qnf$1@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...

-- 
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 - 12:57:46 CST