CODEWITHSUNDEEP
sqloracle-databaseoracle-apexapex
JAY_R
JAY_R

Reputation: 3

Difference between two queries in SQL (Oracle Apex Items)

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.

Upvotes: 0

Views: 118

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Koen's answer gives the general, correct answer for this type of problem.

In your particular instance, however, you can use a simpler query:

SELECT SUM(t.gems)
FROM (SELECT gems FROM tasks
      UNION ALL
      SELECT gems FROM quests
      UNION ALL
      SELECT gems FROM daily_quests
      UNION ALL
      SELECT price * -1 FROM items
) t

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18630

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

Upvotes: 3

The Impaler
The Impaler

Reputation: 48770

Assuming each query produces a column named gems you can do:

with
a as (
  -- query 1 here
),
b as (
  -- query 2 here
)
select a.gems - b.gems as diff
from a
cross join b

Upvotes: 1

Related Questions