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() 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
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.
The SUM() function can total a field of numeric values. Suppose you want to total the Scores field...
SELECT SUM(Scores) FROM Students
The MAX() function return the highest score on the mid-term exam...
SELECT MAX(Scores) FROM Students
Likewise, you can use the MIN() function to return the lowest mid-term exam score..
SELECT MIN(Scores) FROM Students
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:
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)
Set MyConn = Nothing
functions AVG SUM MAX MIN asp