SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 multiple selects to return in single row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peo
Starting Member

Sweden
11 Posts

Posted - 04/25/2013 :  07:47:02  Show Profile  Reply with Quote
Hi. I have (example) three selects requesting average data from the same columns from the same table but with different conditions. I want the result as one row.

select '2013-04-01 01:00' As TSTAMP,
AVG(TT_SECC_IN) As SECC_IN,
AVG(TT_SECC_OUT) as SECC_OUT,
AVG(TT_SECW_IN) as SECW_IN ,
AVG(TT_SECW_OUT) as SECW_OUT
From LOGGEDVALUES
Where PAID='003061' and SMPTIME >= '2013-04-01 00:00' and SMPTIME < '2013-04-01 01:00'

select '2013-04-01 01:00' As TSTAMP,
AVG(TT_SECC_IN) As SECC_IN_2,
AVG(TT_SECC_OUT) as SECC_OUT_2,
AVG(TT_SECW_IN) as SECW_IN_2 ,
AVG(TT_SECW_OUT) as SECW_OUT_2
From LOGGEDVALUES
Where PAID='003061' and EP_COMP > 0 and SMPTIME >= '2013-04-01 00:00' and SMPTIME < '2013-04-01 01:00'

select '2013-04-01 01:00' As TSTAMP,
AVG(TT_SECC_IN) As SECC_IN_3,
AVG(TT_SECC_OUT) as SECC_OUT_3,
AVG(TT_SECW_IN) as SECW_IN_3 ,
AVG(TT_SECW_OUT) as SECW_OUT_3
From LOGGEDVALUES
Where PAID='003061' and EP_COMP > 0 and [STABLE]=1 and SMPTIME >= '2013-04-01 00:00' and SMPTIME < '2013-04-01 01:00'

The first select will always return values, the other to might return NULLs except of course for TSTAMP when condition is not met.

What is the best way to achieve this?



/P

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/25/2013 :  08:00:05  Show Profile  Reply with Quote
--May be this?
select '2013-04-01 01:00' As TSTAMP, 
AVG(TT_SECC_IN) As SECC_IN,
AVG(TT_SECC_OUT) as SECC_OUT,
AVG(TT_SECW_IN) as SECW_IN ,
AVG(TT_SECW_OUT) as SECW_OUT, 
AVG(CASE WHEN EP_COMP > 0 THEN TT_SECC_IN END) As SECC_IN_2,
AVG(CASE WHEN EP_COMP > 0 THEN TT_SECC_OUT END) as SECC_OUT_2,
AVG(CASE WHEN EP_COMP > 0 THEN TT_SECW_IN END) as SECW_IN_2 ,
AVG(CASE WHEN EP_COMP > 0 THEN TT_SECW_OUT END) as SECW_OUT_2, 
AVG(CASE WHEN EP_COMP > 0 and [STABLE]=1 THEN TT_SECC_IN END) As SECC_IN_3,
AVG(CASE WHEN EP_COMP > 0 and [STABLE]=1 THEN TT_SECC_OUT END) as SECC_OUT_3,
AVG(CASE WHEN EP_COMP > 0 and [STABLE]=1 THEN TT_SECW_IN END) as SECW_IN_3 ,
AVG(CASE WHEN EP_COMP > 0 and [STABLE]=1 THEN TT_SECW_OUT END) as SECW_OUT_3
From LOGGEDVALUES 
Where PAID='003061' and SMPTIME >= '2013-04-01 00:00' and SMPTIME < '2013-04-01 01:00'


--
Chandu
Go to Top of Page

peo
Starting Member

Sweden
11 Posts

Posted - 04/25/2013 :  08:35:09  Show Profile  Reply with Quote
Brilliant, thank you.

/P
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/25/2013 :  08:54:18  Show Profile  Reply with Quote
quote:
Originally posted by peo

Brilliant, thank you.
/P

You are welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000