Home BrainDump 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.

By: James Payne
Rating:  / 1
December 10, 2007

SEARCH DEV SHED

TOOLS YOU CAN USE

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.

 >>> More BrainDump Articles          >>> More By James Payne