Solved

Display results of 2 queries in one result

Posted on 2011-03-10
4
390 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2012 AOG and SQL2014 AOG 76 58
Conditions in Where clause 9 46
Need some alteration to below mention query 2 10
Need to trim my database size 9 11
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

713 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