Presenting summary information with splitting the value of a column

Question

Is there a way to bring up the following with the table below:

customer_id | loan_date  | loan_amount | loan_paid | status
------------+------------+-------------+-----------+--------
customer1     04/02/2010   5000          3850        active
customer2     04/02/2010   3000          3000        completed
customer3     04/02/2010   6500          4300        defaulted
...

Avg loan, the standard deviation of all the loans, the number of loans, the total amount of defaulted, and the total amount of collected loans per month. (I have data for about 5 years).

I have no idea of where to start.

Start from reading about aggregate function. In this case - for statistic, like stddev, count, sum. https://www.postgresql.org/docs/14/functions-aggregate.html — Kadet, May 14 '22 at 18:34
The database questions usually follow these steps: 1. data 2. expected output. Also it is a good habit to show of what have you tryied so far. — user14063792468, May 14 '22 at 21:26

score 0 · Accepted Answer · answered May 14 '22 at 21:41

Start like this:

SELECT date_trunc('month', loan_date)
     , avg(loan_amount) AS avg_loan
     , stddev_samp(loan_amount)  AS stddev_samp
     , count(*) AS ct_loans
     , count(*) FILTER (WHERE status = 'defaulted') AS ct_defaulted
     , sum(loan_paid) AS sum_paid
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Then refine. Details are unclear. Not sure what loan_paid signifies exactly, and what you want to sum exactly. And there are multiple measures under the name of "standard deviation" ...

About aggregate functions.

About date_trunc().

About GROUP BY 1:

Concatenate multiple result rows of one column into one, group by another column

About the aggregate FILTER clause:

Aggregate columns with additional (distinct) filters

Presenting summary information with splitting the value of a column

1 Answers1