Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
CIO Insight
Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Anyone looking for a way to modernize legacy data or easily migrate to a more cost-effective database without sacrificing functionality will benefit from this seminar. View the Intro to Advantage Database Server now!
  #1  
Old July 8th, 2002, 02:55 PM
beetrader beetrader is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2002
Location: London/Stockholm
Posts: 4 beetrader User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation 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

Reply With Quote
  #2  
Old July 8th, 2002, 03:51 PM
Waltjp's Avatar
Waltjp Waltjp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2002
Location: NJ, USA
Posts: 91 Waltjp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 1 m 8 sec
Reputation Power: 6
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.

Reply With Quote
  #3  
Old July 8th, 2002, 04:10 PM
NoXcuz's Avatar
NoXcuz NoXcuz is offline
Wiking
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Sep 2000
Location: Sweden
Posts: 3,608 NoXcuz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 h 49 m 27 sec
Reputation Power: 11
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

Reply With Quote
  #4  
Old July 8th, 2002, 04:41 PM
beetrader beetrader is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2002
Location: London/Stockholm
Posts: 4 beetrader User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation 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

Reply With Quote
  #5  
Old July 9th, 2002, 01:41 AM
ajaypatil ajaypatil is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2002
Location: Osaka
Posts: 9 ajaypatil User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 49 m 21 sec
Reputation 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

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > Can I get the average length of a field?


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

 Free IT White Papers!
 
Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

 
Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

 
Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

 
Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

 
Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Web Buyers Guide




© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway