HomeBrainDump Page 2 - SQL: Functioning in the Real World
What You Talkin Bout Willis? - BrainDump
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:
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.
