SQL: AVERAGE, GROUP BY

Shafeen

Information is taken from: http://www.techbookreport.com/index.html

Taking an average is not much more difficult. If we want to find the average Mb of RAM for the different processor types listed in the PC table we could issue the following SQL statement:

SELECT Avg(PC.RAM) AS Memory, PC.Processor
FROM PC
GROUP BY PC.Processor;

Yielding the result:

Memory	Processor
74.66	Pentium 2
64	Pentium Pro

Other aggregate functions include Min, Max, Sum etc, depending on your particular implementation of SQL.
Any WHERE cause will have been applied before the GROUP BY clause takes effect, so it is easily possible to exclude certain records from the aggregate functions. This means, however, that it is not possible to use the WHERE clause to exclude some of the aggregated results. For example if you were interested in looking at the average disk size of machines for those machines not running Windows 95, we would use the WHERE clause to exclude OpSys='Win95', and then the GROUP BY clause to group by OpSys:

SELECT Avg(PC.Disk), PC.OpSys AS Disk
FROM PC
WHERE PC.OpSys<>'Win95'
GROUP BY PC.OpSys;

If we want to apply a selection process on the outcome of the GROUP BY process we have to resort to another type of selection clause. SQL uses the HAVING clause to make selections of data after the GROUP BY process has completed. For example, if we wanted a listing of the average memory per processor type but were only interested if the average were less than or equal to 64Mb, we could code a query as follows:

SELECT DISTINCTROW PC.Processor, Avg(PC.RAM) AS RAM
FROM PC
GROUP BY PC.Processor
HAVING Avg(PC.RAM) <=64;

The HAVING clause can have multiple terms, just as the WHERE clause can, and these can be linked with the normal logical operators AND, OR etc.