how to select multiple columns from a table based on different where clause

Question

I want to combine these 4 queries into one query:

select SUM(AMOUNT) as TOTAL_AMOUNT
from PAYMENT;

select SUM(AMOUNT) as NOT_SETTLED_AMOUNT
from PAYMENT
where STATE = 0;

select SUM(AMOUNT) as SETTLED_AMOUNT
from PAYMENT
where STATE = 1;

SELECT CREATION_DATE
        FROM PAYMENT
        WHERE PAYMENT_ID = (SELECT max(PAYMENT_ID) from PAYMENT)) as LATEST_PAYMENT_DATE;

SELECT MAX(ID) FROM INQUIRY

Answer 1

You can perform all 4 computations in a single table-scan, using conditional aggregation. As for the creation date of the latest payment id, we can use Oracle's keep syntax:

select
    sum(amount) as total_amount,
    sum(case when state = 0 then amount else 0 end) as not_settled_amount,
    sum(case when state = 1 then amount else 0 end) as settled_amount,
    max(creation_date) keep(dense_rank last order by payment_id) as latest_payment_date
from payment

Note that this is more efficient than other approaches that use subqueries and/or union.