April 28, 2011 at 2:42 pm
How about
select
floor(rowid),
sum(case when [status] like '%in-service%' then 1. else 0. end)
/ count(*) as Availability
from test
group by floor(rowid)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2011 at 2:44 pm
LOL nvm. If I actually read what you posted I would had a different answer. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2011 at 2:44 pm
use a subquery ....
select
floor(rowID) as trunk
,max(Availability)
from (select
rowid,
sum(case when [status] like '%in-service%' then 1 else 0 end)
/ (count(*)*1.) as Availability -- the *1. is to force conversion to numeric
from dbo.test
group by rowid) as x
group by
floor(rowID)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 28, 2011 at 2:50 pm
Perfect. You guys are amazing. I always learn so much in these forums.
Thanks both of you for helping!
Howard
April 28, 2011 at 3:08 pm
One more hang up.
Some of the rowid have multiple decimal places. When it gets to those, it tells me it can't convert varchar to float for floor(rowid).
What does that mean?
April 28, 2011 at 3:14 pm
Then use your method of
LEFT(rowid,charindex('.',rowid)-1)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 28, 2011 at 3:23 pm
ah ok. I was thinking if I did that, it would only group by the first digits before the . instead of each full rowid.
Thanks again Jason.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply