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.
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_OUTFrom 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_2From 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_3From 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_3From LOGGEDVALUES Where PAID='003061' and SMPTIME >= '2013-04-01 00:00' and SMPTIME < '2013-04-01 01:00'[/code]--Chandu |
|
|
peo
Starting Member
15 Posts |
Posted - 2013-04-25 : 08:35:09
|
Brilliant, thank you./P |
|
|
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 |
|
|
|
|
|
|
|