CODEWITHSUNDEEP
postgresql
cyberfly
cyberfly

Reputation: 5878

How to minus result of two sum column

I want to minus the sum of two column (expected_payment minus actual_payment).

How do I achieve that?

Thanks

Current query:

select

(SELECT SUM(payment_amount) 
from ticket_payments
WHERE kod = '12010')
as expected_payment,

(SELECT SUM(payment_amount) 
from ticket_payments
WHERE kod = '22010')
as actual_payment

Result

enter image description here

If I modify the query like this it will produce an error

select

(SELECT SUM(payment_amount) 
from ticket_payments
WHERE kod = '12010')
as expected_payment,

(SELECT SUM(payment_amount) 
from ticket_payments
WHERE kod = '22010')
as actual_payment,

expected_payment - actual_payment as balance_payment

Error:

enter image description here

Upvotes: 0

Views: 43

Answers (3)

S-Man
S-Man

Reputation: 23736

Using a subquery (the problem is that at this point you are creating the aliases, so they cannot be known at the same time/level):

demo:db<>fiddle

SELECT 
    *,
    expected_payment - actual_payment AS balanced_payment
FROM
(
    SELECT
        (SELECT <YOUR CODE>) as expected_payment,
        (SELECT <YOUR CODE>) as actual_payment
) s

Upvotes: 2

user330315
user330315

Reputation:

You can't access a column alias on the same level where you defined it. You need to wrap that into a derived table:

select expected_payment, actual_payment, expected_payment - actual_payment as balance_payment
from (
  select (SELECT SUM(payment_amount) 
          from ticket_payments
          WHERE kod = '12010') as expected_payment,
          (SELECT SUM(payment_amount) 
          from ticket_payments
          WHERE kod = '22010') as actual_payment
) as x

Upvotes: 2

IVO GELOV
IVO GELOV

Reputation: 14269

select

(SELECT SUM(payment_amount) 
from ticket_payments
WHERE kod = '12010')
as expected_payment,

(SELECT SUM(payment_amount) 
from ticket_payments
WHERE kod = '22010')
as actual_payment,

(SELECT SUM(payment_amount) 
from ticket_payments
WHERE kod = '12010')
-
(SELECT SUM(payment_amount) 
from ticket_payments
WHERE kod = '22010')
as sum_diff

Upvotes: 1

Related Questions