# SQL: Functioning in the Real World

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.

Functioning in the Real World

Let’s face it, none of us function the way we want to in the real world. Sure, slap a copy of myself in the Sims and the next thing you know I am an in-shape, good looking, millionaire writer with oodles of time, a bevy of chicks from the neighborhood dropping by (and some dudes too, no matter how often I lock the door). I also have a hip house, a sauna, and a soda machine in my make believe Sim world.

Then there’s me in the real world. On a fast track to being the world’s fattest man (look out Guinness here I come), driving a car with no air conditioner in South Florida (aka, the Sun), and the only females that come to my door are trying to sell me cookies.

And if you are reading this article on Saturday, then guess what: you’re in the same boat as me. But never fear. SQL, just like Tony Robbins, can help you out.

We left off last time with the promise of covering our good ole’ pal the Function. In SQL, functions are used for counting and calculations and for saving lazy programmers time. There are two types of functions: Aggregate Functions and Scalar Functions. In this article we will discuss most of the Aggregates, such as how to get the average value of a column, count the values in a column and total them.

We’ll also learn how to find both the minimum and maximum values in a column, using the min and max functions respectively. There’s a lot of learnin’ to do, so let’s get started.

{mospagebreak title=What You Talkin Bout Willis?}

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.

{mospagebreak title=Count}

Remember that crazy vampire from Sesame Street with the obsessive compulsive disorder? Always counting everything. One frightened child, two frightened children, AH AH AH!

SQL has its own Count, though this one isn’t likely to lure your child into an alleyway and drain all its blood. It counts every row that does not have a value of Null and returns the value. Before we see what that looks like in code, let’s add a new value to our table. Our new employee is going to have the Name Go-Go Roboto, have no listed age, and make \$100,000. When you are done, you table should look like this:

 Name Age Salary Ang Ree 92 \$12,000 Godzilla Nokamura 45 \$50,000 Mothra Fujiyama 27 \$80,000 King Kong Wong 19 \$100,000 Go-Go Roboto \$100,000

As part of the upcoming lawsuit, you might want to check to make sure that every employee in the database has an age listed. To do that, you could use the Count function:

Select Count(Age) from Employees

This would return the result:

4

That is because four fields contain data, and the fifth contains a Null value (or no value), and thus is not counted.

Of course, knowing the number of rows with good data is useless unless we know the number of total rows. To get this result, we would use the Count(*) function.

Select Count(*) From Employees

The result in this case would be:

5

Now that we have both the number of rows in our table, and the number of rows missing data from our Age column, we can do the math and know that one row has no data.

But we’re not through with Counting yet! Remember the Count is OCD, and so are we. Let’s fix the database by adding Go-Go Roboto’s age, which is 29.

 Name Age Salary Ang Ree 92 \$12,000 Godzilla Nokamura 45 \$50,000 Mothra Fujiyama 27 \$80,000 King Kong Wong 19 \$100,000 Go-Go Roboto 29 \$100,000

The next step in our lawsuit will be to find out how many young whipper snappers work for the company. We can also use the Count(*) function for this:

Select Count(*) from Employees Where Age < 30

This will show us all of the employees aged 29 or less, which in this case, is 3.

Average age is 45, average salary for younger employees is way higher, and there are more youthful employees than old-timers…look out frivolous lawsuit, here we come.

{mospagebreak title=Count Distinct}

Let’s step away from the lawsuit scenario for a moment. Let’s add a new column to our table called Position. Fill in your database with the added values below.

 Name Age Salary Position Ang Ree 92 \$12,000 Director Godzilla Nokamura 45 \$50,000 Villain Mothra Fujiyama 27 \$80,000 Villain King Kong Wong 19 \$100,000 Hero Go-Go Roboto 29 \$100,000 Actor created solely for toy sales

If we wanted to know how many unique positions there were (or any other unique items) we could use the Count(Distinct) function. As you can see, there are presently 5 positions in the company. What we want to know though is how many positions there are that don’t duplicate themselves.

Select Count (Distinct Position) from Employees

The result:

4

This is because the Villain position is a duplicate, and thus one value is not unique.

{mospagebreak title=Minimum and Maximum}

The Min(Column) and Max(Column) functions return the lowest and highest value in a column, respectively. If we wanted to see the highest pay rate in our Employees table, we would do the following:

Select Max(Salary) from Employees

This would return the value, \$100,000. If we wanted to see the lowest salary, we would do this:

Select Min(Salary) from Employees

The result of this would be \$12,000. Poor Ang Ree.

In Summation

Ha! I fooled you! You though this was the end of the article. Well you were wrong; I was using word play to trick your feeble mind.

You can use the Sum(Column) function to get a total of all the values in a column. If we wanted to know the annual salary of all of the employees in the Employee table, it would go a little something like this:

Select Sum(Salary) from Employees

This would add all of the values in the Salary column, resulting in the total: \$342,000.

Well, that wraps it up for this part. In the next part we will cover the rest of the Aggregate Functions, and learn the Scalar functions as well. Look forward to seeing you.

Till then…