Can I get the average length of a field?

Hi!

Is it possible to get (through an SQL query) a number telling me what's the average length of some fields in a text column? Imagine that newspaper staff want to see what's the average length of an article in their archive. How would you solve this?

I would be very, very thankful for clarity on this.

If I could figure out how much data is in a result set I could divide it by the number of hits to get the average I guess. But I don't know how to do this either. Can it be done?

I'm hoping to be able to connect to all kinds of databases, so in the best case this can be done seemlessly in all dialects of SQL. But if you know a type-specific solution I'd be happy to know as well. I thought this would be simple but I can't find the answer anywhere.

Thanks,
Beetrader

Write a query to select the field you want to check the length of. In Java you could use the length() method from the string class. I'm sure there are similar solutions in other languages.

Once you have the collection of records, loop through it to determine the length of each one. Keep a running total of the length and the number of records processed. The average is the total length/number processed.

Something like this (for MySQL):
SELECT AVG(CHAR_LENGTH(<column>)) AS avgLength FROM <table>

Other db's have functions like length, str_len and similar to do the same. It's also possible to do it in your scripting language, although not as efficient.

//NoXcuz

Thanks both of you!

My application is going to run through the whole newspaper archive looking for one or two words at the click on a webpage so I will be looking hard for a solution where the counting is done on the database side.
As I interpret your solution, Waltjp, I would have to fetch each article as a string into my script (php) and do the counting there?

And as to your solution, NoXcuz, I'm going to test it tomorrow. (Tested for 10 minutes now without success, getting late.) You make it look like this size-measuring function is a common kind of thing with different database standards, and I am obviously delighted to see that. Does anybody know if it is so common it has even been implemented in any database abstraction layer? (I happen to be using PEAR DB under php)

Thanks again
/Beetrader

Why dont you put an additional column with the
length of the article ?

The length can be calculated whenever the article
is inserted or updated.

This will make your query much easier and faster.

Ajay