April 28, 2011 at 1:45 pm
Hi everyone.
I have a list of network devices that write to a log table with a status of up or down every 10 minutes. I must report on what percentage of the reports are up. For example, of 1000 lines, 990 were up (990/1000 = 99% up).
I was thinking of doing this:
select rowid,cast(count(*) as float)/(select cast(count(*) as float) from CustomPollerStatistics_Detail group by rowid) as Availability
from CustomPollerStatistics_Detail
where status like '%in-service%'
group by rowid
of course it does not work because the subquery outputs a long list of results. Somehow I think I have to join the rowid from the top level query with the rowid in the sub query but I can't think how to do it.
Has anyone designed a query like this before?
Thanks for reading.
April 28, 2011 at 1:49 pm
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 CustomPollerStatistics_Detail
group by 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 1:53 pm
For clarification: Don't think of this as a count from two queries. You are querying one table, one time and performing an aggregate depending on the values in that table. This is one example of a Set-Based approach. You only need to "touch" the data one time.
______________________________________________________________________
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 1:54 pm
Thanks for responding Jason.
I get this:
Msg 8120, Level 16, State 1, Line 1
Column 'CustomPollerStatistics_Detail.RowID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
If I put group by rowid at the bottom, I get 0 for every device.
April 28, 2011 at 1:56 pm
Just take the rowid out of the list. You don't want the details, you just want to get the calculation.
_______________________________________________________________
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 1:58 pm
I have a long list of devices and need to know the availability for each of them.
April 28, 2011 at 1:58 pm
If rowID is what it seems to be, unique for each row, then you'll need to remove rowID completely from the SELECT and GROUP BY
______________________________________________________________________
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 1:59 pm
then make sure the group by is still there. 🙂
_______________________________________________________________
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:00 pm
PHXHoward (4/28/2011)
I have a long list of devices and need to know the availability for each of them.
how are they to be grouped? by device type, device name, etc ....
Post the DDL of your table, possibly some sample data and the EXPECTED results. That will make it easier for us to read your mind. 😀
______________________________________________________________________
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:16 pm
Sorry it was not clear. I made this table just now to demonstrate on a very basic level what it looks like.
/****** Object: Table [dbo].[test] Script Date: 04/28/2011 13:12:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[rowid] [varchar](10) NULL,
[status] [varchar](10) NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.1', N'in-service', CAST(0x00009ED300D7FD20 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.1', N'in-service', CAST(0x00009ED300D80A34 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.1', N'in-service', CAST(0x00009ED300D80B0A AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.1', N'out', CAST(0x00009ED300D80B91 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.1', N'in-service', CAST(0x00009ED300D80C08 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'2.1', N'in-service', CAST(0x00009ED300D814A0 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'2.1', N'out', CAST(0x00009ED300D815C5 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'2.1', N'out', CAST(0x00009ED300D816F9 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'2.1', N'in-service', CAST(0x00009ED300D81873 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.2', N'in-service', CAST(0x00009ED300D952A3 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.2', N'in-service', CAST(0x00009ED300D953AB AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.2', N'in-service', CAST(0x00009ED300D956BA AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.3', N'out', CAST(0x00009ED300D95B31 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.3', N'out', CAST(0x00009ED300D95D08 AS DateTime))
INSERT [dbo].[test] ([rowid], [status], [Date]) VALUES (N'1.3', N'out', CAST(0x00009ED300D95ED1 AS DateTime))
/****** Object: Default [DF_test_Date] Script Date: 04/28/2011 13:12:53 ******/
ALTER TABLE [dbo].[test] ADD CONSTRAINT [DF_test_Date] DEFAULT (getdate()) FOR [Date]
GO
if I run this:
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 test
group by rowid
I get 0 for all but the one that is at 100% which displays 1. Must not be displaying the decimals.
April 28, 2011 at 2:18 pm
I was expecting for 1.1 that it would be a decimal as 4 of 5 were up.
April 28, 2011 at 2:21 pm
Sorry, I missed the parentheses around the *1.
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
______________________________________________________________________
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:24 pm
Jason Selburg (4/28/2011)
Sorry, I missed the parentheses around the *1.
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
Actually I think you had it right. But the top is an int so it will produce int output. just need to force the decimal in the top and you should be good to go. 🙂
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 test
group by 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:25 pm
as long as we force one of the two values to numeric. without my parentheses, it was forcing the convert AFTER the division. :hehe:
______________________________________________________________________
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:38 pm
Thanks Jason. That worked and I really learned a lot.
If you don't mind, here is the way that I really have to output it.
The rowid for these network devices are listed as trunked line. channel
so 1.1 and 1.2 are two channels in the same line, in this case line 1.
What I need to find is the availability of each line so if 1.1 was up 80% of the time but 1.2 was up 100% of the time then line 1 was still up 100% of the time.
Is there a way in T-SQL to show the availability of a line by using the max of the channels?
maybe the max of LEFT(rowid,charindex('.',rowid)-1)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply