Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 multiple selects to return in single row

Author  Topic 

peo
Starting Member

15 Posts

Posted - 2013-04-25 : 07:47:02
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-25 : 08:00:05
[code]--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'[/code]

--
Chandu
Go to Top of Page

peo
Starting Member

15 Posts

Posted - 2013-04-25 : 08:35:09
Brilliant, thank you.

/P
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-25 : 08:54:18
quote:
Originally posted by peo

Brilliant, thank you.
/P

You are welcome

--
Chandu
Go to Top of Page
   

- Advertisement -