1

Basically I have a database and I need to extract data on a per month and per company type basis. I'm using psql.

This is what it should look like

Alternatively, it can also look like this

enter image description here

Basically I want the cumulative sum of each type compunded on each other. Is that at all possible?

inputs:

table1 has client, start date, sum

table2 has client, company type

so far, I have this

SELECT DISTINCT(date_trunc('month',start_date)) AS month, company_type, SUM(current_interest) OVER(PARTITION BY company_type ORDER BY start_date) AS cumulative_sum FROM table1 INNER JOIN table2 ON table1.client_id = table2.client_id GROUP BY month, company_type, start_date, current_interest ORDER BY month, company_type DESC;

enter image description here

Right now what I'm getting is something like this. Is there anyway to combine all same company type to show just the total for the whole month instead of per input.

enter image description here

What I want is something like this. Sorry if the title is misleading, not really sure what to call it. Any help is appreciated, Thanks!

Karl Lim
  • 11
  • 3
  • 1
    Sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. –  Sep 30 '20 at 18:44
  • Thanks for editing, I appreciate it! – Karl Lim Sep 30 '20 at 19:12
  • 1
    Does this answer your question? [Calculating Cumulative Sum in PostgreSQL](https://stackoverflow.com/questions/22841206/calculating-cumulative-sum-in-postgresql) – Schwern Oct 01 '20 at 09:35
  • As @Schwern pointed out - you will need to use `sum()` as a Window Function. Use the `OVER ()` clause. https://www.postgresql.org/docs/13/functions-window.html – Jonathan Jacobson Oct 01 '20 at 16:04
  • any idea what should be included in the OVER( ) clause? I'm still having trouble, kindly check my code. Thanks so much! – Karl Lim Oct 01 '20 at 19:49
  • @KarlLim Could you give us some clear sample data and your desired output? It's unclear how you want to accumulate the sums. – Schwern Oct 01 '20 at 20:29
  • Edited the post, thanks so much! What I'm after is just the total per company type for the month. Is that possible? – Karl Lim Oct 01 '20 at 21:16

1 Answers1

0

Yes, use a group by.

select
  date_trunc('month', start_date) as month,
  company_type,
  sum(sum) as sum_per_type
from table1 t1
join table2 t2 on t1.client = t2.client
group by month, company_type
-- To get the specific ordering...
order by month asc, company_type desc
Schwern
  • 153,029
  • 25
  • 195
  • 336
  • my problem is how to get the cumulative sum of just sole propritorship for the first row, and then just the cumulative sum of just partnerships for the second row, and so on. Is that at all possible? – Karl Lim Sep 30 '20 at 19:56
  • That's what `group by month, company_type` does, cumulative sum per month and type. Is it not working? I think you want an `order by`? – Schwern Sep 30 '20 at 20:04
  • What should the sum look like if I want to make it cumulative? Thanks! – Karl Lim Oct 01 '20 at 08:58
  • @KarlLim Have a look at https://stackoverflow.com/questions/22841206/calculating-cumulative-sum-in-postgresql – Schwern Oct 01 '20 at 09:35