simple average question with mysql

Hello,

I know this might be out of rails question, but I could not find any other place to ask :smiley:

select count(url_id) as url_count, user_id from urls_users group by url_id limit 10;

Jamal Soueidan wrote in post #968670:

Hello,

I know this might be out of rails question,

Then don't ask it here.

but I could not find any other place to ask :smiley:

Apparently you didn't look very hard: http://lmgtfy.com/?q=mysql+forum

select count(url_id) as url_count, user_id from urls_users group by url_id limit 10; +-----------+---------+ > url_count | user_id | +-----------+---------+ > 238 | 1 | > 3070 | 2 | > 141 | 3 | > 1 | 7 | > 156 | 8 | > 397 | 11 | > 1 | 15 | > 20 | 16 | > 73 | 17 | > 329 | 18 | +-----------+---------+

How do I get the average url_count on all the users in that table based on url_id?

Read about SQL aggregate functions. Also read about ActiveRecord::Calculations, which abstracts them in Rails.

Thanks for help.

Best,

That was fast :slight_smile:

I tried with avg but this doesn't work as I want.

select avg(count(url_id)) as url_count, user_id from urls_users group by     -> url_id limit 10; ERROR 1111 (HY000): Invalid use of group function

I don't understand why this happend.

Marnen Laibow-Koser wrote in post #968673:

and this one doesn't work either :frowning:

SELECT count(url_id) as url_count, avg(url_count), urls_users.* FROM `urls_users` limit 10; ERROR 1054 (42S22): Unknown column 'url_count' in 'field list'

Either: Keep guessing.... and get added to a lot of email killfiles.

or

Look at the two options Marnen suggested: 1) Go look into the pure SQL functions for this, 2) Look at the ActiveRecord::Calculations methods (at least then your query might have *something* to do with Rails)

select avg(url_count) from ( select count(url_id) as url_count, user_id from urls_users group by url_id limit 10 );

Okay I will do that when I find the correct SQL, then I convert the SQL to rails if this can be done :slight_smile:

Thanks Tim for your SQL.

But this gives me some weird error.

select avg(url_count) from ( select count(url_id) as url_count, user_id from urls_users group by url_id limit 10 );

ERROR 1248 (42000): Every derived table must have its own alias

Okay I had to point out AS something in the end of the SQL.

Thanks for help everyone :smiley:

But this gives me some weird error.

It's not "weird" - it's perfectly normal....

select avg(url_count) from ( select count(url_id) as url_count, user_id from urls_users group by url_id limit 10 );

ERROR 1248 (42000): Every derived table must have its own alias

"AS tablename" - read a SQL reference!