# A Little SQL Help

**URL:** <https://www.ruby-forum.com/t/a-little-sql-help/181296>\
**Category:** Rails\
**Created:** [January 8, 2010, 11:31pm UTC](https://www.ruby-forum.com/t/a-little-sql-help/181296 "2010-01-08T23:31:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![karlshea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/58956e/32.png) [@karlshea](https://www.ruby-forum.com/u/karlshea)\
**Post date:** [January 8, 2010, 11:31pm UTC](https://www.ruby-forum.com/t/a-little-sql-help/181296/1 "2010-01-08T23:31:51Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![karlshea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/58956e/32.png) [@karlshea](https://www.ruby-forum.com/u/karlshea)\
**Post date:** [January 8, 2010, 11:49pm UTC](https://www.ruby-forum.com/t/a-little-sql-help/181296/2 "2010-01-08T23:49:24Z")

</div>

On Jan 8, 2010, at 5:30 PM, Karl S. wrote:

> WFBQ Pending  
> 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 B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![karlshea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/58956e/32.png) [@karlshea](https://www.ruby-forum.com/u/karlshea)\
**Post date:** [January 9, 2010, 3:06am UTC](https://www.ruby-forum.com/t/a-little-sql-help/181296/3 "2010-01-09T03:06:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![karlshea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/58956e/32.png) [@karlshea](https://www.ruby-forum.com/u/karlshea)\
**Post date:** [January 9, 2010, 12:20pm UTC](https://www.ruby-forum.com/t/a-little-sql-help/181296/4 "2010-01-09T12:20:10Z")

</div>

## Ballin!

Jason S.  
“I’m going to live forever or die trying.”

On Fri, Jan 8, 2010 at 4:48 PM, Rob B.

---

<div class="post-metadata">

**Author:** ![karlshea](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/58956e/32.png) [@karlshea](https://www.ruby-forum.com/u/karlshea)\
**Post date:** [January 9, 2010, 3:54am UTC](https://www.ruby-forum.com/t/a-little-sql-help/181296/5 "2010-01-09T03:54:36Z")

</div>

On Jan 8, 2010, at 9:04 PM, Karl S. 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

> > “Nutty”  
> > 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-  
> > removed\_email\_address@domain.invalid.  
> > To unsubscribe from this group, send email to removed\_email\_address@domain.invalid  
> > .  
> > For more options, visit this group at [http://groups.google.com/group/rubyonrails-talk?hl=en](http://groups.google.com/group/rubyonrails-talk?hl=en)  
> > .

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid