Question: I have below 2 tables and I have to, write a query to display the sum of the total amount received through the credit card and cheque. Give the alias names as 'total\_credit' and 'total\_cheque' respectively.

table\_payment  

id (PK)  

user\_id  

amount  

transaction\_type\_id (FK)

table\_transaction\_type  

id (PK)  

type 

Expected Output:

```
total_credit| total_cheque     
Value a     | Value B

```

I tried with below query but I getting values in different rows

```
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 group by tt.type

```

Actual\_Output:

```
total_credit| total_cheque     
Value A     |     
            | Value B

```

not sure how to get Value B in first row along with Value A, please advice.

Answer: 
```
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;

```
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

```