Now for some more functions that will help you get the data you want out of the database. And finally, we will live dangerously and start deleting stuff.
What Are Aggregate Functions?
Wouldn’t it be great if SQL provided you with some basic functions for averaging, counting or finding the largest or smallest number in a set of numbers? Well, I’m sure you’ve guessed that aggregate functions do exactly that.
Instead of just filtering your data, aggregate functions actually return a value. That value can be either a calculated value or a value from your data depending on the function. Here are some of the most used aggregate functions:
Function Description MAX() This returns a selected column's highest value MIN() This returns a selected column's lowest value SUM() This returns the sum of a selected column AVG() This returns the average value of a selected column COUNT()
This counts the number of rows in a selected column
Let’s take a look at each function and see how useful they can be.
First, the MIN and MAX functions. These two functions are polar opposites of one another. MAX return the highest value in a column while MIN returns the lowest value.
So, when would you use MAX and MIN? You could use MAX and MIN for all sorts of mathematical purposes like finding the largest sale on a given day or which student had the lowest test score in a class. An example of this would be:
SELECT MAX(sale_total)
FROM daily_receipts
WHERE sales_date = '01/01/2002'
This example gets the MAXimum sale from the column “sale_total” in the table “sales_log” for sales made on 1/1/2002.
You can also use MAX and MIN to find dates. For example, I just recently built a website that displays webcast seminar events and allows users to register. The home page highlights the next event scheduled and displays a description of the event. Here is a portion of the SQL that I used to get the data I wanted:
SELECT MIN(event_date)
FROM event
WHERE event_date > GETDATE();
This gives me the date of the event that is the closest to today’s date that hasn’t already past.
Can I use MIN and MAX with a string of characters? Some DBMSs support using MIN and MAX on strings. The result would be either the first or last row if the column were sorted alphabetically.
Let’s SUM it up now. Alright, bad pun but I couldn’t resist. As my friend would say, “that’s 2/3 of a pun … P.U.” Anyway, SUM does exactly what you would think, it adds together the all of the rows in a given column. For example, you may use SUM to total the sales for a given day in a retail store. Here’s an example:
SELECT SUM(sales)
FROM daily_receipts
WHERE sales_date = '01/01/2002
‘;
Then there’s AVG. Average is about as straight forward as it gets. Average is like a combination of SUM and COUNT with a little division thrown in. We’ll use AVG on our example above to get the average sale for a given day:
SELECT AVG(sales)
FROM daily_receipts
WHERE sales_date = '01/01/2002';
Then there’s COUNT. You should remember COUNT from Part 5. You will probably find yourself using this function quite a bit. You could use COUNT to find the total number of orders in a day, how many people signed up for class, how many messages are posted to a discussion group and many other instances. Here’s an example of using COUNT to get the number of messages posted to a discussion group:
SELECT COUNT(message) AS message_count
FROM discussion_group
WHERE category = 'SQL';
Like I’ve said before, every DBMS is different and each one will have its own set of unique functions. So, if you are looking for something a little more specific than the basic functions we went through above, consult your DBMS documentation.
What’s So Great About DISTINCT?
We’ll continue on with the discussion group scenario. Let’s say that your discussion group has grown to over 20 categories. The administrator of your discussion group keeps adding categories right and left. Instead of manually adding category names to all the forms and drop-down list boxes in your web by hand you decide it would be better to simply retrieve the information from the database and populate your forms automatically.
How would you go about this? If you do a SELECT on the “category” column of your table you will have a very long list of categories with lots of duplication. To accomplish your task you will want to use the DISTINCT clause. Here’s how you would get the category list using DISTINCT:
SELECT DISTINCT category
FROM discussion_group;
Now you will have a complete list of categories with no duplicates because only unique values are returned.
Can I use DISTINCT with some of the aggregate functions above? You sure can, however, it doesn’t make sense with some of the functions. For example, Why use DISTINCT with MIN or MAX when they are returning a single value anyway?
With the other functions you may find a use for DISTINCT. For example, what if you wanted to count the number of categories in your discussion group? You might combine COUNT with DISTINCT like this:
SELECT COUNT(DISTINCT category)
FROM discussion_group;
This will give you the total number of unique categories in your discussion group.
How Do I Delete Stuff?
Now for the scary part, deleting data. Obviously, removing data from your database is necessary from time to time. It can be a very scary thing, though. What if you accidentally delete some very important data? What if you accidentally delete an entire table?
These are all very real possibilities, so take great care when issuing a DELETE to your database. Unfortunately, deleting data is all too simple. Here is an example of how to delete a person from our sample database we created way back in Part 2:
DELETE FROM contacts
WHERE contact_id = 3;
In this case DELETE remove the row in table “contacts” where the “contact_id” is equal to 3. That means Bill Murray is history.
So, what happens if I forget the WHERE clause? Well, that would be very bad news because you most likely just wiped out all of the data in the contacts table. The WHERE clause is so very important here. If you don’t get it exactly right you could end up deleting something you really wanted to keep. If you forget the WHERE clause altogether you will probably find yourself scrambling to locate the last backup of your database. So, being careful with DELETE cannot be stressed enough!
In some cases, DELETE may even be restricted by the Database Administrator to prevent the accidental deletion of data.
