Solved

Display results of 2 queries in one result

Posted on 2011-03-10
4
386 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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
Perfect! This was just what I needed. (The others were unions and would have stacked the data rather than in columns.)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now