CODEWITHSUNDEEP
postgresql
Dodo
Dodo

Reputation: 31

Selecting data from different rows

I want select the opening balance of the first month and the closing balance of the last month in PostgreSQl then now i the sum of income as total income and sum of expenditure as total expenditure on the same row... Here is my data below

ID OPENING_BAL INCOME EXPENDITURE CLOSING_BAL COUNCIL_NAME DATE_COMPILED
21   5000.00   1000.00  2000.00   6000.00     BAKONE       2017-04-28
22   6000.00   1000.00  4000.00   9000.00     BAKONE       2017-05-31
23   9000.00   1500.00  2000.00   9500.00     BAKONE       2017-06-30

Upvotes: 2

Views: 52

Answers (1)

Patrick
Patrick

Reputation: 32374

You can do this with a window function:

SELECT DISTINCT council_name,
       first_value(opening_bal) OVER w AS opening_bal,
       sum(income) OVER w AS total_income,
       sum(expenditure) OVER w AS total_expenditure,
       last_value(closing_balance) OVER w AS closing_balance
FROM my_table
WINDOW w AS (PARTITION BY council_name ORDER BY date_compiled
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

This does the summing over the entire period in your table. You could add a WHERE clause to select a range of date_compiled, if so desired.

Upvotes: 3

Related Questions