Hello,
I know this might be out of rails question, but I could not find any
other place to ask
select count(url_id) as url_count, user_id from urls_users group by url_id limit 10;
Hello,
I know this might be out of rails question, but I could not find any
other place to ask
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
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
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
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
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
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!