Can someone give me a hand with the sql (mysql works for me) for this rails project... Data similar to this: Station Status WKRP Pending WKRP Pending WKRP Deleted WIBC Deleted WFBQ Pending WFBQ Nutty What I need is an sql statement that will count the various status states and return a *single row* for each station. Like: Station Pending Deleted Nutty WKRP 2 1 0 WIBC 0 1 0 WFBQ 1 0 1 I can get the counts of each station/status in individual lines and loop through the array counting the various status states, but the runtime for that is growing and I''m looking for a better solution before it becomes a problem. Ideas or suggestions? -- 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 Jan 8, 2010, at 5:30 PM, Karl Smith wrote:> Can someone give me a hand with the sql (mysql works for me) for this > rails project... > > Data similar to this: > > Station Status > WKRP Pending > WKRP Pending > WKRP Deleted > WIBC Deleted > WFBQ Pending > WFBQ Nutty > > What I need is an sql statement that will count the various status > states and return a *single row* for each station. Like: > > Station Pending Deleted Nutty > WKRP 2 1 0 > WIBC 0 1 0 > WFBQ 1 0 1 > > I can get the counts of each station/status in individual lines and > loop through the array counting the various status states, but the > runtime for that is growing and I''m looking for a better solution > before it becomes a problem. > > > Ideas or suggestions? > --SELECT station, SUM(CASE WHEN status = ''Pending'' THEN 1 ELSE 0 END) AS "Pending", SUM(CASE WHEN status = ''Deleted'' THEN 1 ELSE 0 END) AS "Deleted", SUM(CASE WHEN status = ''Nutty'' THEN 1 ELSE 0 END) AS "Nutty" FROM your_table GROUP BY station; How does that work for you? -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org -- 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.
Thanks Rob, that''s pretty good. Close to one of my attempts. But what if status is not static? The distinct values of `status` change in value and quantity... which it does. On Jan 8, 3:48 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote:> > SELECT station, > SUM(CASE WHEN status = ''Pending'' THEN 1 ELSE 0 END) AS > "Pending", > SUM(CASE WHEN status = ''Deleted'' THEN 1 ELSE 0 END) AS > "Deleted", > SUM(CASE WHEN status = ''Nutty'' THEN 1 ELSE 0 END) AS "Nutty" > FROM your_table > GROUP BY station; > > How does that work for you? > > -Rob > > Rob Biedenharn http://agileconsultingllc.com > R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org-- 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 Jan 8, 2010, at 9:04 PM, Karl Smith wrote:> Thanks Rob, that''s pretty good. Close to one of my attempts. > > But what if status is not static? The distinct values of `status` > change in value and quantity... which it does.Then you can build the SQL after first getting the distinct values of the status. Or do the analysis in ruby after getting the raw [station, status] data. -Rob> > > On Jan 8, 3:48 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote: >> >> SELECT station, >> SUM(CASE WHEN status = ''Pending'' THEN 1 ELSE 0 END) AS >> "Pending", >> SUM(CASE WHEN status = ''Deleted'' THEN 1 ELSE 0 END) AS >> "Deleted", >> SUM(CASE WHEN status = ''Nutty'' THEN 1 ELSE 0 END) AS >> "Nutty" >> FROM your_table >> GROUP BY station; >> >> How does that work for you? >> >> -Rob >> >> Rob Biedenharn http://agileconsultingllc.com >> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org > . > For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en > . > >Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org -- 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.