how to combine two queries in sql server

Question

how to combine two queries in sql server

jprabha.d

Ten Centuries

Points: 1086
More actions
December 7, 2009 at 6:45 am

#136742

Hi ,I have two queries ,i need to combine two of them and get the results
The below is the queries
select avg(Cast([["\\GBEDC-APPSP01\Memory\Pages/sec"]]] as float)) AS MemoryPages,
avg(Cast([["\\GBEDC-APPSP01\Memory\Available MBytes"]]] as INT)) AS MemoryAvailableMBytes ,
avg(Cast([["\\GBEDC-APPSP01\Processor(_Total)\% Processor Time"]]] as float)) AS ProcessorTime,
convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103) as DateTime,
FROM [WSS_Content_EuroTraining].[dbo].[ERS_MOSS_P01_Monitor]
where ["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"] between '11/25/2009' and '11/30/2009'
group by Convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103)
select avg(Cast(["\\GBEDC-APPSP02\Memory\Pages/sec"] as float)) AS MemoryPages_1,
avg(Cast(["\\GBEDC-APPSP02\Memory\Available MBytes"] as INT)) AS MemoryAvailableMBytes_1 ,
avg(Cast(["\\GBEDC-APPSP02\Processor(_Total)\% Processor Time"] as float)) AS ProcessorTime_1,
convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103) as DateTime
FROM [WSS_Content_EuroTraining].[dbo].[ERS_MOSS_P02_Monitor]
where ["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"] between '11/25/2009' and '11/30/2009'
group by Convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103)
But i need the result in the this format
MemoryPages MemoryAvailableMbytes ProcessorTime DateTime MemoryPages_1
Totally all the columns need to be displayed in the table.I dont what to use Union Function as it gives the data in only 4 columns.
Thanx In Advanced
Jaya

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

It looks like you want to do an JOIN between the two queries, or Join the underlying tables and query them both.

To do this you need to provide the column that contains the data that defines the relationship between the two tables usually a PK->FK relationship, eg. ProcessorID or similar

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

How many readings do you have per day?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jprabha.d Ten Centuries Points: 1086 More actions · Answer 3

there is no any PK or Fk relationship between this two tables ,or any column been identical,

thanx

Jaya

steveb. SSC-Forever Points: 46733 More actions · Answer 4

If there are no relationship then how do you want to combine them?

Post some sample data if you want some more specific help

Lowell SSC Guru Points: 323518 More actions · Answer 5

does this work? if your SQLs return just one line, this look good, otherwise it'd do a cartesian join unless you add a where statement.

SELECT

X.MemoryPages, X.MemoryAvailableMbytes, X.ProcessorTime, X.[DateTime], Y.MemoryPages_1

FROM

(

select

avg(Cast([["\\GBEDC-APPSP01\Memory\Pages/sec"]]] as float)) AS MemoryPages,

avg(Cast([["\\GBEDC-APPSP01\Memory\Available MBytes"]]] as INT)) AS MemoryAvailableMBytes ,

avg(Cast([["\\GBEDC-APPSP01\Processor(_Total)\% Processor Time"]]] as float)) AS ProcessorTime,

convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103) as DateTime

FROM [WSS_Content_EuroTraining].[dbo].[ERS_MOSS_P01_Monitor]

where ["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"] between '11/25/2009' and '11/30/2009'

group by Convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103)

) X,

(

select

avg(Cast(["\\GBEDC-APPSP02\Memory\Pages/sec"] as float)) AS MemoryPages_1,

avg(Cast(["\\GBEDC-APPSP02\Memory\Available MBytes"] as INT)) AS MemoryAvailableMBytes_1 ,

avg(Cast(["\\GBEDC-APPSP02\Processor(_Total)\% Processor Time"] as float)) AS ProcessorTime_1,

convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103) as DateTime

FROM [WSS_Content_EuroTraining].[dbo].[ERS_MOSS_P02_Monitor]

where ["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"] between '11/25/2009' and '11/30/2009'

group by Convert(varchar(13),["(PDH-CSV 4 0) (GMT Daylight Time)(-60)"],103)

) Y

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jprabha.d Ten Centuries Points: 1086 More actions · Answer 6

If i run your quey ,i get a constant values for the column MemoryPages_1

say for any dateTime ,the column values seems to be the same..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

jprabha.d (12/7/2009)
If i run your quey ,i get a constant values for the column MemoryPages_1
say for any dateTime ,the column values seems to be the same..
[MemoryPages MemoryAvailableBytes ProcessorTIme Datetime MemoryPages_1
9.948098824 1538 1.228312605 11/25/2009 00 7.122892628
3.002041067 1723 0.825656218 11/25/2009 01 7.122892628
3.064543224 2554 0.881698261 11/25/2009 02 7.122892628
4.988632522 2615 0.707224869 11/25/2009 03 7.122892628
8.236930287 2505 1.338280872 11/25/2009 04 7.122892628
3.452333892 2422 0.891138064 11/25/2009 05 7.122892628

What is the name of your 5th column, reading from left to right?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lowell SSC Guru Points: 323518 More actions · Answer 8

if the first query returns 6 rows, and the second returns 1, that is the results of the cartesian join we warned you about.

you have to determine what joins the two tables together to get any sensible data our of combining the two queries. no join would give you the ugly data you posted, where it doesn't seem to help with any analysis.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!