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

TABLE OF CONTENTS:
  1. SQL: Functioning in the Real World
  2. What You Talkin Bout Willis?
  3. Count
  4. Count Distinct
  5. Minimum and Maximum
By: James Payne
Rating: starstarstarstarstar / 1
December 10, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

BRAINDUMP ARTICLES

- Apple Founder Steve Jobs Dies
- Steve Jobs` Era at Apple Ends
- Google's Chrome Developer Tool Updated
- Google's Chrome 6 Browser Brings Speed to th...
- New Open Source Update Fedora 13 is Released...
- Install Linux with Knoppix
- iPad Developers Flock To SDK 3.2
- Managing a Linux Wireless Access Point
- Maintaining a Linux Wireless Access Point
- Securing a Linux Wireless Access Point
- Configuring a Linux Wireless Access Point
- Building a Linux Wireless Access Point
- Migrating Oracle to PostgreSQL with Enterpri...
- Demystifying SELinux on Kernel 2.6
- Yahoo and Microsoft Create Ad Partnership

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: