Join two select statements

[WaveOut]

I have the following two select statements:

SELECT ts,max FROM aggregates WHERE name like '3B-HP-DRM-SKIN-1-T' AND ts between '14-JUN-06 08:32' AND '14-JUN-06 22:26';

SELECT ts,max FROM aggregates WHERE name like '3B-HP-DRM-SKIN-2-T' AND ts between '14-JUN-06 08:32' AND '14-JUN-06 22:26';

(note difference is in the name - skin-1 and skin-2)

Rather than display these in one line like so:

Skin 1

ts max
-------------------- --------------
14-JUN-06 08:33:00.0 566.632
14-JUN-06 08:34:00.0 567.24
14-JUN-06 08:35:00.0 568.62
14-JUN-06 08:36:00.0 569.921
14-JUN-06 08:37:00.0 570.814
14-JUN-06 08:38:00.0 571.274

Skin 2

14-JUN-06 08:33:00.0 567.536
14-JUN-06 08:34:00.0 568.507
14-JUN-06 08:35:00.0 569.452
14-JUN-06 08:36:00.0 570.463
14-JUN-06 08:37:00.0 571.306
14-JUN-06 08:38:00.0 571.788


I need them side by side like this:

ts skin1 max Skin2 max
-------------------- -------------- --------------
14-JUN-06 08:33:00.0 566.632 567.536
14-JUN-06 08:34:00.0 567.24 568.507
14-JUN-06 08:35:00.0 568.62 569.452
14-JUN-06 08:36:00.0 569.921 570.463
14-JUN-06 08:37:00.0 570.814 571.306
14-JUN-06 08:38:00.0 571.274 571.788

I'm thinking some type of join should be used here.


Dave

 

[bborissov]

SELECT Tbl1.Ts, Tbl1.Max AS Skin1Max, Tbl2.Max AS Skin2Max
FROM 
(SELECT ts,max FROM aggregates WHERE name like '3B-HP-DRM-SKIN-1-T' AND ts between '14-JUN-06 08:32' AND '14-JUN-06 22:26') Tbl1
INNER JOIN 
(SELECT ts,max FROM aggregates WHERE name like '3B-HP-DRM-SKIN-2-T' AND ts between '14-JUN-06 08:32' AND '14-JUN-06 22:26') Tbl2 ON Tbl1.Ts = Tbl2.Ts

If the field Name is varchar you must use [=] instead of LIKE, it is faster.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[vbkris]

or


SELECT ts,case when name like '3B-HP-DRM-SKIN-1-T' max else 'NA' end as [skin1 max],case when name like '3B-HP-DRM-SKIN-2-T' max else 'NA' end as [skin2 max] FROM aggregates WHERE ts between '14-JUN-06 08:32' AND '14-JUN-06 22:26';


Note:
I have not tested the SQL, but the concept is the same.

Known is handfull, Unknown is worldfull

 

[bborissov]

or

SELECT ts,
       SUM(case when name like '3B-HP-DRM-SKIN-1-T' max else 0 end) as [skin1 max],
       SUM(case when name like '3B-HP-DRM-SKIN-2-T' max else 0 end) as [skin2 max]
FROM aggregates
WHERE ts between '14-JUN-06 08:32' AND '14-JUN-06 22:26'
GROUP BY Ts

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[WaveOut]

Thanks for the help with the code. Im using the following:

SELECT Tbl1.Ts, Tbl1.Max AS Skin1Max, Tbl2.Max AS Skin2Max
FROM 
(SELECT ts,max FROM aggregates WHERE name like '3B-HP-DRM-SKIN-1-T' AND ts between '14-JUN-06 08:32' AND '14-JUN-06 22:26') Tbl1
INNER JOIN 
(SELECT ts,max FROM aggregates WHERE name like '3B-HP-DRM-SKIN-2-T' AND ts between '14-JUN-06 08:32' AND '14-JUN-06 22:26') Tbl2 ON Tbl1.Ts = Tbl2.Ts

The results look like this:

Ts Skin1Max Skin2Max
-------------------- -------------- --------------
14-JUN-06 07:33:00.0 516.857 518.063
14-JUN-06 07:34:00.0 517.756 518.566

now what I would like to do is sum skin1max and skin2max like so:

Ts Skin1Max Skin2Max Tot
-------------------- -------------- -------------- -----
14-JUN-06 07:33:00.0 516.857 518.063 1036.92
14-JUN-06 07:34:00.0 517.756 518.566 1036.32

How can I sum Skin1Max and Skin2Max?

 

[WaveOut]

I was able to figure it out.

SELECT Tbl1.Ts, Tbl1.Max AS Skin1Max, Tbl2.Max AS Skin2Max, Tbl2.Max+Tbl1.Max as SkinSum
FROM 
(SELECT ts,max,sum FROM aggregates WHERE name = '3B-HP-DRM-SKIN-1-T' AND ts between '14-JUN-06 07:32' AND '14-JUN-06 22:26') Tbl1
INNER JOIN 
(SELECT ts,max,sum FROM aggregates WHERE name = '3B-HP-DRM-SKIN-2-T' AND ts between '14-JUN-06 07:32' AND '14-JUN-06 22:26') Tbl2 ON Tbl1.Ts = Tbl2.Ts;

Thanks for everyone's help!

Dave