Simple average question with mysql

Rails
1

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;
±----------±--------+
| 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?

Thanks for help.

2

Jamal S. 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: LMGTFY - Let Me Google That For You

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

3

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:

Jamal S. 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: LMGTFY - Let Me Google That For You

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

4

On 15 December 2010 19:35, Jamal S. [email protected] wrote:

and this one doesn’t work either :frowning:

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)

5

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’

6

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

7

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

Thanks for help everyone :smiley:

8

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

9

On 15 December 2010 19:43, Jamal S. [email protected] wrote:

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!