#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    London/Stockholm
    Posts
    4
    Rep Power
    0

    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
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    12
    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.
    - Walt

    Will code for food.
  4. #3
  5. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    26
    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
    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    London/Stockholm
    Posts
    4
    Rep Power
    0
    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
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    Osaka
    Posts
    9
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo