Explore BrainMass

A detailed example of a SQL select statement using GROUP BY

The schema of the DB is as follows:

Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, *DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(*PaNum, *AcNum)
Field(FieldNum, ID, Title)
Interest(*FieldNum, *AcNum, Descrip)

Question 6:
Which fields have interests shared by academics from the same department? Output the field number, field ID and title, and the total number of academics who have interests.

I'm struggling with the conditions in the first part of the question.

Thanks Mick


Solution Preview

This key to this query is the GROUP BY clause that can be used with a SELECT statement. The GROUP BY clause instructs the DBMS to combine rows of data based on the columns that are specified in the GROUP BY clause.

Consider the query:

SELECT * FROM interest

This query will return all of the rows from the interest table. There are lots of rows with the same FieldNum value. If the GROUP BY clause is added then we only get one row per unique FieldNum.

SELECT * FROM interest GROUP BY FieldNum

The power of GROUP BY is in the fact that you can aggregate the combined rows. The SELECT can show the number of rows that have the same FieldNum:

SELECT *, count(FieldNum) FROM ...

Solution Summary

This solution provides a detailed example of how to use the GROUP BY clause with a SQL SELECT statement.