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)

http://lmgtfy.com/?q=sql+avg+count

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!