Join 2 tables using Oracle with SUM query?

Question

I have 2 queries

Q1:

SELECT tc_ghy07,tc_ghy12
FROM tc_ghy_file
WHERE tc_ghy02 = 'DNF-000414'
      AND tc_ghy01=TO_CHAR(YEAR(CURRENT_DATE))

Result:

+----------+----------+
| TC_GHY07 | TC_GHY12 |
+----------+----------+
|       16 |        0 |
+----------+----------+

Q2:

SELECT ( SUM(DECODE(tc_soa38,NULL,0,tc_soa38)+ decode(tc_soa39,NULL,0,tc_soa39*1.5) 
+ DECODE(tc_soa40,NULL,0,tc_soa40 * 1.6) + decode(tc_soa41,NULL, 0,tc_soa41 * 2) +  
    decode(tc_soa42,NULL,0,tc_soa42
* 2.1) + DECODE(tc_soa43, NULL, 0,tc_soa43 * 2.7) 
+ DECODE(tc_soa44,NULL, 0,tc_soa44 * 3) + DECODE(tc_soa45, NULL,0,tc_soa45 *
3.9)) ) / 8 AS result
FROM tc_soa_file
WHERE tc_soa33 = 'BU'
      AND tc_soa04 = 'DNF-000414'
      AND year(tc_soa07) = year(current_date);

Result:

+--------+
| RESULT |
+--------+
|     20 |
+--------+

How can I join 2 these tables into one query?

I have tried Union All or Cross join but it's not right. Thanks for helping

I want to query 2 these query in one query and the result will like this

+-----------+----------+--------+
|  TC_GHY07 | TC_GHY12 | RESULT |
+-----------+----------+--------+
|        16 |        0 |     20 |
+-----------+----------+--------+

Edit: Join condition is tc_ghy02=tc_soa04

We'll never know if you don't tell us what the join condition is. Also, reduce our effort by providing some sample data and expected result. — Kaushik Nayak
– Kaushik Nayak, Commented Jul 23, 2019 at 5:47
YEAR(CURRENT_DATE) is invalid for Oracle - are you sure you are using that? — user330315
– user330315, Commented Jul 23, 2019 at 6:32
I have tried select year(CURRENT_DATE) from dual and result is 2019, it's ok, but how about select year(SYSDATE) from dual — Diao Vũ
– Diao Vũ, Commented Jul 23, 2019 at 6:35
As @KaushikNayak said, you have to have join condition to join your two result tables. And one more comment, for the sake of clean code, you should change DECODE(tc_soa39,NULL,0,tc_soa39*1.5) to NVL(1.5 * tc_soa39, 0) or COALESCE(1.5 * tc_soa39, 0) and so on. — Pham X. Bach
– Pham X. Bach, Commented Jul 23, 2019 at 6:59
If select year(CURRENT_DATE) from dual works for you, then you are not using Oracle: dbfiddle.uk/… — user330315
– user330315, Commented Jul 23, 2019 at 7:18

IDA · Accepted Answer · 2019-07-23 09:06:30Z

3

You can try to use this query

SELECT a.tc_ghy07,
       a.tc_ghy12,
       r.result
  FROM tc_ghy_file a
 INNER JOIN (select b.tc_soa04,
                    (SUM(DECODE(b.tc_soa38, NULL, 0, b.tc_soa38) +
                         decode(b.tc_soa39, NULL, 0, b.tc_soa39 * 1.5) +
                         DECODE(b.tc_soa40, NULL, 0, b.tc_soa40 * 1.6) +
                         decode(b.tc_soa41, NULL, 0, b.tc_soa41 * 2) +
                         decode(b.tc_soa42, NULL, 0, b.tc_soa42 * 2.1) +
                         DECODE(b.tc_soa43, NULL, 0, b.tc_soa43 * 2.7) +
                         DECODE(b.tc_soa44, NULL, 0, b.tc_soa44 * 3) +
                         DECODE(b.tc_soa45, NULL, 0, b.tc_soa45 * 3.9))) / 8 AS result 
             from tc_soa_file b
             where b.tc_soa33 = 'BU'
                   and year(b.tc_soa07) = year(current_date)
             Group by tc_soa04
             ) r on r.tc_soa04 = a.tc_ghy02
 WHERE a.tc_ghy02 = 'DNF-000414'
   AND a.tc_ghy01 = TO_CHAR(YEAR(CURRENT_DATE))

edited Jul 23, 2019 at 9:06

answered Jul 23, 2019 at 6:58

IDA

763 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Diao Vũ Over a year ago

Thanks for your help, but because of sum, so it's error not a single-group group function but when I added GROUP BY a.tc_ghy07, a.tc_ghy12, it's a emty query, no result

Collectives™ on Stack Overflow

Join 2 tables using Oracle with SUM query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related