Friday, April 29, 2011

Working with SQLite in AIR: Part 6

Today we will learn about functions in SQL.

It is possible to use functions when using SQL commands. There are a number of functions available to work with in the SQLite engine, and they can be found on the official site here and here.

We won't cover all of the functions that are on on that list in this tutorial, but I'll give you the general idea of how to work with them.

For example, let's take the first function that is given on the official site under the "Aggregate Functions" list.

The function is avg(X), which returns an average value of the given ones. Here's what the documentation says on this function:

"The avg() function returns the average value of all non-NULL X within a group. String and BLOB values that do not look like numbers are interpreted as 0. The result of avg() is always a floating point value as long as at there is at least one non-NULL input even if all inputs are integers. The result of avg() is NULL if and only if there are no non-NULL inputs. "

Alright, we can use that with our contacts database to calculate the average age of the people in our database. Of course, you could simply pass all the values to AS3 and do the calculations there - but why bother if we can do that from start?

Anyways, to use a function, you need to use it with a SELECT command. Let's go:

SELECT avg(age) FROM contacts

And that's it! Now the selected value is the average age of our contacts. Actually, the command will return a table with one column that has the name avg(age). Yes, this combination of symbols is not the best column name. No worries though, we can change the name easily, using the "AS" keyword.

Observe:

SELECT avg(age) AS averageAge FROM contacts

Now the returned table will consists of one column that has a name of averageAge. Brilliant!

But, say, you also have a column "Town" in your contacts database, and you wish to return a table of average ages for each location separately. No problem here - just use the GROUP BY expression.

SELECT town, avg(age) as averageAge FROM contacts GROUP BY town

Now you have some very basic knowledge in the SQL language. This is enough to finally start working with AIR and SQLite databases. We'll do that next time!

Thanks for reading.


Related:

Working with SQLite in AIR: Part 1
Working with SQLite in AIR: Part 2
Working with SQLite in AIR: Part 3
Working with SQLite in AIR: Part 4
Working with SQLite in AIR: Part 5
Working with SQLite in AIR: Part 7
Working with SQLite in AIR: Part 8
Working with SQLite in AIR: Part 9
Working with SQLite in AIR: Part 10
Working with SQLite in AIR: Part 11
Working with SQLite in AIR: Part 12

No comments:

Post a Comment