Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Display results of 2 queries in one result

Posted on 2011-03-10
4
388 Views
Last Modified: 2012-05-11
I am hoping fo r aquick turnaround on this question, probably really easy....

I nned to produce a query which shows the total number of servers per building AND the total number of servers down in the same result.

Example:

BLDG          TotalServers         DownServers
--------------------------------------------------------------
123                   20                              0
456                   16                              2

The queries I am using are:
For all servers
select bldg, count(*) as TotalServers from nodes
where vendor = 'windows' and status = 1
group by bldg

For down servers
select bldg, count(*) as DownServers from nodes
where vendor = 'windows' and status <> 1
group by bldg

Can these be combined?
0
Comment
Question by:edrz01
  • 2
4 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 35094796
Use UNION to combine them

select bldg, count(*) as TotalServers from nodes
where vendor = 'windows' and status = 1
group by bldg

UNION ALL

select bldg, count(*) as DownServers from nodes
where vendor = 'windows' and status <> 1
group by bldg

0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 35094812
Remove the first group by, sorry

select bldg, count(*) as TotalServers from nodes
where vendor = 'windows' and status = 1

UNION ALL

select bldg, count(*) as DownServers from nodes
where vendor = 'windows' and status <> 1
group by bldg
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 35094837

select isnull(a.bldg, b.bldg), isnull(TotalServers, 0) as 'UpServers', isnull(DownServers, 0) as 'DownServers' from (
select bldg, count(*) as TotalServers from nodes
where vendor = 'windows' and status = 1
group by bldg) a
full outer join (
select bldg, count(*) as DownServers from nodes
where vendor = 'windows' and status <> 1
group by bldg) b
on a.bldg = b.bldg
0
 

Author Closing Comment

by:edrz01
ID: 35095521
Perfect! This was just what I needed. (The others were unions and would have stacked the data rather than in columns.)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question