Complicated PostgreSQL query to group by multiple columns

Question

I have PostgreSQL table with client_id, date, smp_number, vs_type, vs_amount:

client_id  |date          |smp_number   |vs_type   |vs_amount
1233455    |2017.05.02    |1234/0123    |debit     |10000
1233455    |2017.05.03    |1236/0123    |credit    |1000
1233455    |2017.05.04    |1234/0123    |credit    |1000
1233455    |2017.05.07    |1235/0123    |debit     |15000
1233456    |2017.05.02    |1234/0123    |debit     |11000
1233456    |2017.06.03    |1236/0123    |credit    |10000

What I want to achieve is to group by client_id and month and then to get the total debit sum and the total credit sum by month in distinct columns and the last smp_number in each month.

The result should be:

client_id  |date          |debit_amount |credit_amount   |last_smp
1233455    |2017.05.01    |25000        |2000            |1235/0123 
1233456    |2017.05.01    |10000        |11000           |1234/0123 
1233456    |2017.06.01    |10000        |11000           |1236/0123

Hope this makes sense, any help is appreciated. I have searched all the relevant topics here.

FlexYourData · Accepted Answer · 2022-02-03 21:02:08Z

3

Your sample data does not produce your expected output, but something like this should work.

WITH dat
AS
(
SELECT client_id, 
date_trunc('month',transaction_date)::date as transaction_month,
transaction_date,
smp_number,
vs_type,
vs_amount
FROM the_table
)

SELECT x.client_id
, x.transaction_month
, y.max_date_in_month
, MAX(CASE WHEN x.transaction_date = y.max_date_in_month THEN x.smp_number ELSE NULL END) AS smp_number
, SUM(CASE WHEN vs_type = 'debit' THEN vs_amount ELSE 0 END) AS debit_amount
, SUM(CASE WHEN vs_type = 'credit' THEN vs_amount ELSE 0 END) AS credit_amount
FROM dat x
INNER JOIN (SELECT client_id, transaction_month, MAX(transaction_date) AS max_date_in_month
            FROM dat
            GROUP BY 1,2) y ON (x.client_id,x.transaction_month) = (y.client_id,y.transaction_month)
GROUP BY 1,2,3;

dbfiddle.uk

answered Feb 3, 2022 at 21:02

FlexYourData

2,4941 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brisamh · Accepted Answer · 2022-02-03 22:12:20Z

0

What you're looking to do is pivot the table, aka the crosstab function

https://www.postgresql.org/docs/current/tablefunc.html

edited Feb 3, 2022 at 22:12

user330315

answered Feb 3, 2022 at 20:53

brisamh

114 bronze badges

Collectives™ on Stack Overflow

Complicated PostgreSQL query to group by multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related