A Little SQL Help

Rails
1

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?

2

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
[email protected]

3

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.

4

Ballin!

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

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

5

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-
[email protected].
To unsubscribe from this group, send email to [email protected]
.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en
.

Rob B. http://agileconsultingllc.com
[email protected]