postgres sql query to convert group by result in multiple columns

Tags: , , , ,



I have two tables

  1. financial_account having columns account_name
  2. financial_transaction having columns transaction_date,transaction_type, transaction_amount

I need data as SUM(transaction_amount) where transaction_type=’A’ under column SUM_A and SUM(transaction_amount) under column SUM_B where transaction_type=’B’

I took reference of this stackoverflow post , wrote query as below :

select fa.account_name ,
to_char(current_date - interval '1' month, 'Mon-YY') as "Previous Month",
SUM(case when ft.transaction_type='A' then ft.transaction_amount else 0 end) as "SUM_A",
SUM(case when ft.transaction_type='B' then ft.transaction_amount else 0 end) as "SUM_B"

from financial_transaction ft

join financial_account fa on fa.account_name = 'XYZ'

where ft.transaction_date  >= date_trunc('month', now()) - interval '1 month' and
      ft.transaction_date  < date_trunc('month', now())

group by ft.transaction_type,fa.account_name

having ft.transaction_type in ('A','B')

However, this query is generating data in two rows

enter image description here

I needed data in single row format.

enter image description here

How can i get data in 1 one row format?

Answer

Considering your query is working properly, you can write your query like below:

select 
fa.account_name ,
to_char(current_date - interval '1' month, 'Mon-YY') as "Previous Month",
SUM(case when ft.transaction_type='A' then ft.transaction_amount else 0 end) as "SUM_A",
SUM(case when ft.transaction_type='B' then ft.transaction_amount else 0 end) as "SUM_B"
from financial_transaction ft
join financial_account fa on fa.account_name = 'XYZ'
where ft.transaction_date  >= date_trunc('month', now()) - interval '1 month' and
      ft.transaction_date  < date_trunc('month', now()) and ft.transaction_type in ('A','B')

group by 1,2

You can write it like below also:

select 
fa.account_name ,
to_char(current_date - interval '1' month, 'Mon-YY') as "Previous Month",
SUM(ft.transaction_amount) filter (where ft.transaction_type='A') as "SUM_A",
SUM(ft.transaction_amount) filter (where ft.transaction_type='B') as "SUM_B",
from financial_transaction ft
join financial_account fa on fa.account_name = 'XYZ'
where ft.transaction_date  >= date_trunc('month', now()) - interval '1 month' and
      ft.transaction_date  < date_trunc('month', now()) and ft.transaction_type in ('A','B')
group by 1,2


Source: stackoverflow