fetch average from multiple rows (both numeric and text

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Darla
Forum Newbie
Posts: 16
Joined: Mon Feb 12, 2007 8:27 am

fetch average from multiple rows (both numeric and text

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Two queries: One to get the average age, the other to count the names picking the top score.
Darla
Forum Newbie
Posts: 16
Joined: Mon Feb 12, 2007 8:27 am

Post 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"?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.)
Darla
Forum Newbie
Posts: 16
Joined: Mon Feb 12, 2007 8:27 am

Post 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?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

remove the comma after "GROUP BY description"
Darla
Forum Newbie
Posts: 16
Joined: Mon Feb 12, 2007 8:27 am

Post 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
Post Reply