Hello, I know this might be out of rails question, but I could not find any other place to ask :D 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. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 :DApparently 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, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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:> 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 :D
>
> 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,
> --
> Marnen Laibow-Koser
> http://www.marnen.org
> marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
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'' -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 15 December 2010 19:35, Jamal Soueidan <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> and this one doesn''t work either :(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) -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 ); -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Okay I had to point out AS something in the end of the SQL. Thanks for help everyone :D -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 15 December 2010 19:43, Jamal Soueidan <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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! -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.