Oracle query to display 2 sum fields from same column and calculated on different field conditions from different table

What you're looking to do is "pivot" the data, and Oracle provides a pivoting function:

WITH 
   T 
AS 
(
   select tt.type, pt.amount
     from payment pt
     join transaction_type tt on tt.id = pt.transaction_type_id
)
SELECT 
   *
FROM 
   T
PIVOT 
(
   SUM(amount) 
   FOR 
      (type) 
   IN 
      ('Credit Card', 'Cheque')
);

Further examples can be found here: http://www.dba-oracle.com/t_pivot_examples.htm

select user_id, sum(case when tt.type='Credit Card' the amount end) as total_credit, sum(case when tt.type='Cheque' the amount end) as total_credit 
from table_payment p
inner join table_transaction_type tt
on p.transaction_type_id = tt.id
group by user_id;

select  (SELECT sum(pt.amount FROM from payment pt 
join transaction_type tt on pt.transaction_type_id = tt.id 
WHERE tt.type = 'Credit Card'
group by tt.type) as "total_credit"
, (SELECT sum(pt.amount FROM from payment pt 
join transaction_type tt on pt.transaction_type_id = tt.id 
WHERE tt.type = 'Cheque'
group by tt.type) as "total_cheque"

I considered using it as two sub queries, but it looks like we're not joining on any other ID or anything.

Full disclosure: I'm not familiar with Oracle, but this query will definitely work in T-SQL, and I believe it would work in MySQL, so I don't see why it wouldn't work in Oracle, too.

can't you just add a max to it?

  SELECT MAX (SUM (CASE WHEN tt.TYPE = 'Credit Card' THEN pt.amount END))
             AS "total_credit",
         MAX (SUM (CASE WHEN tt.TYPE = 'Cheque' THEN pt.amount END))
             AS "total_cheque"
    FROM payment pt JOIN transaction_type tt ON pt.transaction_type_id = tt.id
GROUP BY tt.TYPE

here is a sample with fake data you can run in oracle.

WITH
    transaction_type
    AS
        (SELECT 1 id, 'Credit Card' AS TYPE FROM DUAL
         UNION ALL
         SELECT 2, 'Cheque' FROM DUAL),
    payment
    AS
        (SELECT 500 AS amount, 1 AS transaction_type_id FROM DUAL
         UNION ALL
         SELECT 300, 2 FROM DUAL
         UNION ALL
         SELECT 200, 1 FROM DUAL
         UNION ALL
         SELECT 400, 2 FROM DUAL)
  SELECT MAX (SUM (CASE WHEN tt.TYPE = 'Credit Card' THEN pt.amount END))
             AS "total_credit",
         MAX (SUM (CASE WHEN tt.TYPE = 'Cheque' THEN pt.amount END))
             AS "total_cheque"
    FROM payment pt JOIN transaction_type tt ON pt.transaction_type_id = tt.id
GROUP BY tt.TYPE

result

 total_credit total_cheque
 700          700

don't group by tt.type - that is why you get that result. If you need to group by user_id (as in one of the solutions) then do; if you need a result with exactly one row and two columns, aggregated (summed) over all users, don't group by ANYTHING, you don't need a GROUP BY clause. (I believe the SQL Standard doesn't allow that; Oracle definitely does, but if that bothers you, you can add something trivial, like GROUP BY NULL.)

Simply use the code without group by clause

select  sum(case when tt.type = 'Credit Card' then pt.amount end) as
total_credit,  sum (case when tt.type = 'Cheque' then pt.amount end)
as total_cheque from payment pt join transaction_type tt on
pt.transaction_type_id = tt.id