Difference between two queries in SQL (Oracle Apex Items)

Question

I have the two following queries in different items in an APEX application:

---

ITEM 1:

SELECT SUM(t.gems)
  FROM (SELECT gems
          FROM tasks
         UNION ALL
        SELECT gems
          FROM quests
         UNION ALL
        SELECT gems
          FROM daily_quests
         ) t

---

ITEM 2:

SELECT SUM(price) FROM items

---

They both return a number correctly, but I want to subtract them in one statement. I tried to use

SELECT TO_NUMBER(ITEM1)-TO_NUMBER(ITEM2) FROM DUAL

but it didn't work.

Do you have any suggestions? I am a bit new to APEX and SQL.

Thank you in advance.

Answer 1

Use a CTE (Common table expression) for each of the queries, then CROSS JOIN them.

WITH t_gems (sum_gems) AS
(
SELECT SUM(t.gems)
  FROM (SELECT gems
          FROM tasks
         UNION ALL
        SELECT gems
          FROM quests
         UNION ALL
        SELECT gems
          FROM daily_quests
         ) t
), t_items (sum_price) AS
(
  SELECT SUM(price) FROM items
)
SELECT t_gems.sum_gems - t_item.sum_price 
  FROM t_gems CROSS JOIN t_items