tester tester - 6 months ago 10
SQL Question

Data insertion on join using sql

I have two table t1 and t2. I need to the aggregated value of "COUNT" and the time in a third table from each of these tables.

t1
:

COUNT TIME_AH
2 17-MAY-16 12:00:00
3 17-MAY-16 12:00:00
2 17-MAY-16 12:00:00
12 17-MAY-16 12:00:00
2 17-MAY-16 12:00:00
12 17-MAY-16 12:00:00


t2
:

COUNT TIME_ZH
1 17-MAY-16 12:00:00
22 17-MAY-16 12:00:00
3 17-MAY-16 12:00:00
4 17-MAY-16 12:00:00


t3
:

OUTPUT_TABLE

COUNT_AH COUNT_ZH TIME_AH
19 30 17-MAY-16 12:00:00


I have tried the below code, but its giving incorrect output.

select sum(t1.count), sum(t2.count), sysdate
from t1,
t2
where TIME_AH = (trunc(sysdate,'hh'));


Please help.

Answer

You can do this with a UNION instead of a JOIN and a CASE EXPRESSION:

SELECT SUM(CASE WHEN f.timetype = 'AH' THEN f.count else 0 END) as count_ah,
       SUM(CASE WHEN f.timetype = 'ZH' THEN f.count else 0 END) as count_zh,
       sysdate
FROM(
    SELECT t.count,t.time_ah,'AH' as TimeType FROM t1 t
    UNION ALL
    SELECT s.count,s.time_zh,'ZH' FROM t2 s) f
WHERE f.TIME_AH =  trunc(sysdate,'hh');