Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Unanswered: assistance with sql query

    Thanks in advance, I am trying to display the count of uptimes and downtimes in a single query. i started with something like
    SELECT DISTINCT servername,
    (SELECT COUNT(*)
    FROM pingtable
    WHERE (status = '0')) AS Uptime,
    (SELECT COUNT(*)
    FROM pingtable
    WHERE (status <> '0')) AS DownTime
    FROM pingtable

    but this gives me the
    server1 7 2
    server1 7 2
    ...

    Table layout and data:
    servername status

    server1 up
    server1 up
    server1 down
    server2 up
    server2 up
    server2 up
    server3 down
    server3 up
    server3 up

    the output I would like to have is

    Server UpCount DownCount
    Server1 2 1
    Server2 3 0
    Server3 2 1

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Lookup crosstab queries in books online. Near the bottom is some sample code you can modify for your needs.

    You'll end up with something like this:

    Select Server,
    sum(Case Status when 0 then 1 else 0) Uptime,
    sum(Case Status when <> 0 then 1 else 0) Downtime
    From PingTable
    Group by Server

    I'm not sitting at a server console now, so I had to draft it from memory and it probably has syntax errors in it, but you should be able to get the idea.

    blindman

  3. #3
    Join Date
    Jul 2003
    Posts
    3

    beautiful... Thanks so much

    Thanks so much for holding my hand there,
    Here is the final query that worked perfectly
    Select Server,
    sum(Case Status when 0 then 1 else 0 end) as Uptime,
    sum(Case Status when 0 then 0 else 1 end) as Downtime
    From eladmin.PingstatsNT
    Group by Server



    Originally posted by blindman
    Lookup crosstab queries in books online. Near the bottom is some sample code you can modify for your needs.

    You'll end up with something like this:

    Select Server,
    sum(Case Status when 0 then 1 else 0) Uptime,
    sum(Case Status when <> 0 then 1 else 0) Downtime
    From PingTable
    Group by Server

    I'm not sitting at a server console now, so I had to draft it from memory and it probably has syntax errors in it, but you should be able to get the idea.

    blindman

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    FYI, if your Status field always holds zeros or ones, set it's data type to bit to ensure that your code will always work correctly.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •