postgres sql query to convert group by result in multiple columns

Question

I have two tables

financial_account having columns account_name
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

I needed data in single row format.

How can i get data in 1 one row format?

In group by clause replace group by ft.transaction_type,fa.account_name with group by 1,2 and move ft.transaction_type in ('A','B') in where clause. It seems you have 2 different transaction_type — Akhilesh Mishra
– Akhilesh Mishra, Commented Nov 17, 2020 at 10:40
Your join condition is wrong. Should be on ft.account_name = fa.account_name. The query will become more readable if you use SUM(ft.transaction_amount) filter (where ft.transaction_type = 'A') as "SUM_A" — Stefanov.sm
– Stefanov.sm, Commented Nov 17, 2020 at 10:46

GMB · Accepted Answer · 2020-11-17 10:48:51Z

Basically, you want to remove ft.transaction_type from the group by clause, so all rows of the same account are grouped together.

Let me pinpoint that your query seems to be missing a join condition between the transactions and accounts.

I would write the query as:

select 
    fa.account_name,
    trunc(current_date - interval '1' month) 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
inner join financial_account fa on ??
where 
    fa.account_name = 'XYZ'
    and ft.transaction_date  >= date_trunc('month', current_date) - interval '1 month' 
    and ft.transaction_date  < date_trunc('month', current_date)
    and ft.transaction_type in ('A','B')
group by fa.account_name

Changes to your original code:

Fixed the group by clause
I represented that missing join condition as ??.
The condition on the transaction type should belong to the where clause rather than the having clause
The conditional sums can be simplified with the standard filter clause

Akhilesh Mishra · Accepted Answer · 2020-11-17 10:44:27Z

0

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

answered Nov 17, 2020 at 10:44

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

2 Comments

user2800089 Over a year ago

both the solution worked. what does group by 1,2 suggest?

Akhilesh Mishra Over a year ago

In Group by 1, 2 1 and 2 represent first and second column of your select. i.e. 1 means fa.account_name

Collectives™ on Stack Overflow

postgres sql query to convert group by result in multiple columns

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related