Page 1 of 1
fetch average from multiple rows (both numeric and text
Posted: Wed Feb 14, 2007 12:22 pm
by Darla
Hello
Just wondering the best way to fetch average values from a table. I wish to fetch both the average age and the most commonly used name from a table which has the fields
age name
21 michelle
15 sean
22 lisa
65 michelle
43 stan
etc.
Anyone have an idea what is the simplest and best way to do this? I guess I can use the AVG-function for the age, but how about the names? (From the above example the name michelle should stand out as the result). Are there some way to do this in php or sql?
Darla
Posted: Wed Feb 14, 2007 12:37 pm
by feyd
Two queries: One to get the average age, the other to count the names picking the top score.
Posted: Wed Feb 14, 2007 12:42 pm
by Darla
OK thanks, the AVE query is fine but you don't happen to have an example of how the count query could look? Would it also be possible to determine the most popular name even if there are double names like "Sue Anne"?
Posted: Wed Feb 14, 2007 12:52 pm
by feyd
It's been a while since I've had to run such a query, but it should look similar to
Code: Select all
SELECT someField FROM someTable GROUP BY someField, ORDER BY COUNT(someField) DESC, someField LIMIT 1
The last "someField" is optional, but it would force the earliest alphabetical name to be selected (if several names have the same count and it's wanted.)
Posted: Wed Feb 14, 2007 1:09 pm
by Darla
Thanks, it seems logical. I get a syntax error with this and I cannot see why though, here's the query i use:
Code: Select all
SELECT description FROM dataset GROUP BY description, ORDER BY COUNT(description) DESC, description LIMIT 1
I get a 1064 syntax error, according to mysql the error is here somewhere:
'ORDER BY COUNT(description) DESC, description LIMIT 1'
Any ideas?
Posted: Wed Feb 14, 2007 3:05 pm
by Kieran Huggins
remove the comma after "GROUP BY description"
Posted: Wed Feb 14, 2007 3:59 pm
by Darla
Did not work either it seems, get this error: #1111 - Invalid use of group function
However I got another one that works now, so problem solved:
Code: Select all
SELECT description, count(description) AS count FROM dataset GROUP BY description ORDER BY count DESC LIMIT 1