SQL: Functioning in the Real World - What You Talkin Bout Willis? (
Page 2 of 5 )
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:
|
Function Name |
What it Does |
|
AVG(column) |
Gives you the average value of the column |
|
COUNT(column) |
Counts the number of rows with data in a column |
|
COUNT(*) |
Counts the number of selected rows |
|
FIRST(column) |
Gives the value of the first record in a specified field |
|
LAST(column) |
Gives the value of the last record in a specified field |
|
MAX(column) |
Gives the maximum value in a column |
|
MIN(column) |
Gives the minimum value in a column |
|
SUM(column) |
Sums up the value of a column |
Average
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:
|
Name |
Age |
Salary |
|
Ang Ree |
92 |
$12,000 |
|
Godzilla Nokamura |
45 |
$50,000 |
|
Mothra Fujiyama |
27 |
$80,000 |
|
King Kong Wong |
19 |
$100,000 |
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.
