I have a table called fact_trip which has a column as fare_final and I want to find the difference between values.

SQL> desc fact_trip
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRIP_UUID                                 NOT NULL VARCHAR2(20)
 DATESTR                                            DATE
 PRODUCT_TYPE_NAME                                  VARCHAR2(20)
 CITY_ID                                            NUMBER
 DRIVER_UUID                                        VARCHAR2(50)
 IS_COMPLETED                                       VARCHAR2(10)
 ETA                                                NUMBER
 ATA                                                NUMBER
 UFP_FARE                                           NUMBER(4,2)
 FARE_FINAL                                         NUMBER(4,2)

So when I do this I'm getting NULL as output:

SQL>  select sum(fare_final) from fact_trip where to_char(datestr, 'W')=1 - (select sum(fare_final) from fact_trip where to_char(datestr, 'W')=2);

SUM(FARE_FINAL)
---------------

I even tried doing those select queries individually like this:

SQL> select sum(fare_final) from fact_trip where to_char(datestr, 'W')=1;

SUM(FARE_FINAL)
---------------
         1821.6

SQL> select sum(fare_final) from fact_trip where to_char(datestr, 'W')=2;

SUM(FARE_FINAL)
---------------
             67

Which is of course fetching result. But then when I run those queries for what I want a difference of them, it's showing NULL. Like I want difference of them (1821.6 - 67). Can anybody tell what's wrong in it? Thank You!

CodePudding user response：

Should be

SELECT SUM (
          CASE
             WHEN TO_CHAR (datestr, 'W') = 1 THEN  fare_final
             WHEN TO_CHAR (datestr, 'W') = 2 THEN -fare_final
          END)
  FROM fact_trip;

I don't have your table so I'll illustrate it on Scott's EMP, computing the total for analysts and clerks:

SQL>   SELECT job, sal FROM emp ORDER BY job;

JOB              SAL
--------- ----------
ANALYST         3000  --> total for analysts is 6000
ANALYST         3000
CLERK           1300  --> total for clerks   is 4150
CLERK            950
CLERK            800  --> difference: 6000 - 4150 = 1850
CLERK           1100
MANAGER         2850
MANAGER         2975
MANAGER         2450
PRESIDENT       5000
SALESMAN        1500
SALESMAN        1250
SALESMAN        1250
SALESMAN        1600

14 rows selected.

SQL> SELECT SUM (
  2            CASE WHEN job = 'ANALYST' THEN  sal
  3                 WHEN job = 'CLERK'   THEN -sal
  4            END) total
  5    FROM emp;

     TOTAL
----------
      1850

SQL>

CodePudding user response：

SQL is not a worksheet, where you may evaluate any expression. You cannot use an expression as a SQL command: you have to eitherselect an expression from something, or calculate it in PL/SQL block.

You want to calculate the difference, so you assumed your code is: subquery - subquery. But because of described above this is not syntactically correct command, and parser tries to find a parse tree which is syntactically correct. And it actually finds one:

select sum(fare_final)
from fact_trip
where to_char(datestr, 'W') = (
  1 - (
    select sum(fare_final)
    from fact_trip
    where to_char(datestr, 'W') = 2
  )
)

Of course, such week doesn't exist and you get null as a result.

To turn your code into a correct command, the simplest way is to select the expression from a dual table with appropriate brackets to specify calculation precedence:

select
  (select sum(fare_final) from fact_trip where to_char(datestr, 'W')='1')
  - (select sum(fare_final) from fact_trip where to_char(datestr, 'W')='2') as res
from dual

But more performant and set-based approach would be:

select
  sum(
    case to_char(datestr, 'W')
      when '1' then 1
      when '2' then -1
    end * fare_final
  ) as res
from fact_trip
where to_char(datestr, 'W') in ('1', '2')

Below is the sample code with results of all that was described above:

select * from t

ID | VAL
-: | --:
 1 |   3
 2 |   6
 3 |   9

select sum(val)
from t
where id = 1 - (
  select sum(val)
  from t
  where id = 2
)

| SUM(VAL) |
| -------: |
|     null |

select
  (
(select sum(val)
from t
where id = 1) - (
  select sum(val)
  from t
  where id = 2
)
  ) as q
from dual

|  Q |
| -: |
| -3 |

select
  sum(
    case id
     when 1 then 1
     when 2 then -1
    end * val
  ) as res
from t
where id in (1, 2)

| RES |
| --: |
|  -3 |

db<>fiddle here