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
fetch average from multiple rows (both numeric and text
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
It's been a while since I've had to run such a query, but it should look similar to
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.)
Code: Select all
SELECT someField FROM someTable GROUP BY someField, ORDER BY COUNT(someField) DESC, someField LIMIT 1Thanks, it seems logical. I get a syntax error with this and I cannot see why though, here's the query i use:
I get a 1064 syntax error, according to mysql the error is here somewhere:
'ORDER BY COUNT(description) DESC, description LIMIT 1'
Any ideas?
Code: Select all
SELECT description FROM dataset GROUP BY description, ORDER BY COUNT(description) DESC, description LIMIT 1'ORDER BY COUNT(description) DESC, description LIMIT 1'
Any ideas?
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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:
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