A couple of months or so ago, I began talking about SQL functions and what they're used for. This article picks up where I left off. It defines the two types of functions, and begins to explain them in great detail.
Poor Arnold; in addition to being vertically challenged, he was also beset with frequent ear infections, and never knew what his brother Willis was talking about. Fortunately for him, Mr. Drummond was looted up, and all Arnold really needed to understand was that money talks. And when Arnold wanted to count the oodles of money he would have once old man Drummond kicked the bucket, well...he could have used Aggregate functions to help with that.
An aggregate function helps summarize large volumes (or even small ones, really) of data. If you have a collection of phone numbers you've collected and want to know how many you have (let's face it, you're a programmer, so you don't) or you want to get a count on the number of days you've spent alone and eating a giant bag of Doritos, you could do just that.
(Just as a side note: the word "Doritos" is actually in the built-in spell check of Microsoft Word).
Below is a list of some aggregate functions and what they can do:
What it Does
Gives you the average value of the column
Counts the number of rows with data in a column
Counts the number of selected rows
Gives the value of the first record in a specified field
Gives the value of the last record in a specified field
Gives the maximum value in a column
Gives the minimum value in a column
Sums up the value of a column
I don't have to tell you what average means; you've been that way your entire life. I mean come on, you're no James Marsden. And if you are, please don't sue me for using your name.
The average function in SQL returns the average value of a given column. Let's say we have a table that lists a person's name, age, and salary and we want to see the average age of the employees listed in the database. Want to follow along? Okay, go ahead and create a table named Employees with the data below:
King Kong Wong
Clearly this company has an age discrimination suit waiting to happen. But never mind that for now. I think our table lists a realistic situation, don't you? Coincidentally, all names are fictitious, and any resemblance to real names is purely coincidental.
Here's the code for the average age:
Select AVG(Age) from Employees
This would result in the answer: 45.75.
Now let's say you are gearing up for that age discrimination lawsuit and need some more data to back you up. Let's say you want to see the average salary of anyone over 30 years old:
Select AVG(Salary) From Employees WHERE Age>30
Your answer would be $31,000. You might also wish to see the average salary of those under thirty. To do that, simply reverse the criteria:
Select AVG(Salary) From Employees WHERE Age<30
Which results in: $90,000. A clear case for age discrimination.