The REALSQLDatabase supports the Group By command so
select Name,count(*) cnt from People group by Name order by cnt desc
should produce a record set with the count of each different Name.
Regards,
Neil.
On 6 Mar 2006, at 12:16, Tom Benson wrote:
hmmm... most common name etc are a bit of a stumper. DB's are god
for string literal matches or even likenesses, but asking a DB to
give up the most common entry for a field is not a popular request.
One way to do this would be a incursive loop such as the following,
though there are probably more efficient methods. This method is
very inefficient, it takes each value from every row for the
specified FieldValue and finds out how many other rows have the
same value, then returns the field value that is most common.
Function MostCommonFieldValue(fieldName as string, tableName as
String) As String
dim n, total,maxMatch, maxIndex as integer
dim r as recordset
dim match as string
r = db.SQLselect("SELECT COUNT(*) FROM "+ tableName)
total = r.integervalue
for n = 1 to total
r = db.SQLselect("SELECT "+fieldName+" FROM "+tableName+"
WHERE id = 'n'")
r = db.SQLselect("SELECT COUNT(*) FROM "+tableName+" WHERE "+
fieldName+" = "+r.idxfield(1).stringValue)
if r.idxfield(1).integerValue > maxMatch then
maxMatch = r.idxfield(1).integervalue
maxIndex = n
end
next
r = db.SQLselect("SELECT "+fieldName+" FROM "+tableName+" WHERE id
= 'n'")
return r.idxfield(1).stringValue
End Function
Average of babies is easy is you have a nice easy column to work
with like" number_of_children" or similar.
If so it would be a simple task of saying:
SELECT AVG(number_of_children) FROM people [WHERE surname LIKE
'identifier']
('WHERE' clause is optional)
On 06/03/2006, at 10:08 PM, Trausti Thor Johannsson wrote:
Hey, I know this is possible in Ansi SQL, but don't know how :oops:
Not good with sub-queries. I use RealSQL (Sqlite) and RB 2006r1 pro
I have a people database, and would like to do some statistics,
like find the most common name, least common name, and if possible
find out AVG of babies people have. The record looks like this
people:
ID int, name varchar, mother int, father int (simplified, but
should do).
mother and father link to the ID ofcourse.
Could someone help me with this SQL magic ? I know how to do this
by code, but when you have 50k+ items, that would take days for a
recursive subrutine to run (Mac Mini G4) :)
Best regards,
Trausti
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
All questions and answers unless otherwise stated are in Relation
to Mac OS X 10.4 and later.
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
