Using Aggregate Functions

Being able to summarize data from your tables can can go a long way in maximizing the potential of your online database.

There are several built-in functions that can aid you in summarizing your data. * Be sure to read the "NOTE" near the bottom of the page.

COUNT()
AVG()
SUM()
MAX()
MIN()

Count()

COUNT() is probably the most useful of these functions and can be used to count the number records in your database.

SELECT COUNT(*) FROM Students

This will count ALL records in the Students table

SELECT COUNT(StudentLName) FROM Students

This example counts the number of last names that are in the Students table. However, this will show a count of all last names, even duplicates.

SELECT COUNT(StudentLName) FROM Students WHERE StudentLName = 'Jones'

This will count the number of records with Jones as a last name...

To get a count of last names without having redundancy you can use the DISTINCT keyword. Thus...

SELECT COUNT(DISTINCT StudentLName) FROM Students

AVG()

Using the function AVG() will show the AVERAGE of values in a field. Suppose you have a field named Scores. In the Scores field are the scores of a mid-term exam. You can average these scores using AVG()...

SELECT AVG(Scores) FROM Students

Naturally, this can only be used with numeric data types. NULLs are ignored.

SUM()

The SUM() function can total a field of numeric values. Suppose you want to total the Scores field...

SELECT SUM(Scores) FROM Students

MAX()

The MAX() function return the highest score on the mid-term exam...

SELECT MAX(Scores) FROM Students

MIN()

Likewise, you can use the MIN() function to return the lowest mid-term exam score..

SELECT MIN(Scores) FROM Students

NOTE!

To make these functions work you have to create an alias...
Below is an example of a database that tracks page hits and then SUMS them all:

<%
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "demo"

sSQL = "SELECT SUM(Page_Hits) AS Total_Page_Hits FROM sitePages"
'AS Total_Page_Hits sets the alias
Set RS = MyConn.Execute(sSQL)

'now you refer to your alias
Total_Page_Hits = RS(0)
Response.Write ("Total Page Hits = " & Total_Page_Hits)

MyConn.Close
Set MyConn = Nothing
%>

functions AVG SUM MAX MIN asp