1

I am new to window functioning in postgresql. I have 10 columns in my data set and i want to do the running total based on the MM-YYYY but keep fail to get the correct amount. I tried to manual download the data and sum and there have no problem.

Sample Data as below

+--------------+--------+--------------+---------+-----------+--------------+-------------+------------+--------------+-------------+
| document_no  |  type  |  year_month  |  accty  |  account  |  gl_account  |  doc_date   |    amt     |  pstng_date  |  entry_date |
+--------------+--------+--------------+---------+-----------+--------------+-------------+------------+--------------+-------------+
|   100014096  |  AB    |  2019/01     |  D      |  A        |      123456  |  12/1/2019  |  75008     |  12/1/2019   |  12/1/2019  |
|   100014096  |  AB    |  2019/02     |  D      |  A        |      123457  |  12/2/2019  |  5008      |  12/2/2019   |  12/2/2019  |
|   100014098  |  AB    |  2019/03     |  D      |  B        |      123458  |  12/3/2019  |  93745     |  12/3/2019   |  12/3/2019  |
|   100014098  |  AB    |  2019/04     |  D      |  B        |      123459  |  12/4/2019  |  3745      |  12/4/2019   |  12/4/2019  |
|   100014106  |  AB    |  2019/05     |  D      |  C        |      123460  |  1/5/2019   |  2434047   |  1/5/2019    |  1/5/2019   |
|   100014106  |  AB    |  2019/06     |  D      |  C        |      123461  |  1/6/2019   |  434047    |  1/6/2019    |  1/6/2019   |
|   100015139  |  AB    |  2019/06     |  D      |  C        |      123462  |  1/6/2019   |  11000     |  1/6/2019    |  1/6/2019   |
|   100015987  |  AB    |  2019/06     |  D      |  D        |      123463  |  1/6/2019   | -22124.85  |  1/6/2019    |  1/6/2019   |
+--------------+--------+--------------+---------+-----------+--------------+-------------+------------+--------------+-------------+

When i tried to use window function to sum it and partition over account and order by date then i cant get the right answer.

Example:

Feb-19 running_total should be (75008 + 5008 = 80016)‬  
Mar-19 running_total should be (75008 + 5008 + 93745 = 173761)‬
Apr-19 running_total should be (75008 + 5008 + 93745 + 3745 = 177506)‬

Below is my sql code, any hints? I have tried those solution found at forum but still to fail get the answer

WITH temp AS(
SELECT 
    (year_month || '/01')::date AS file_date,
    SUM(amt) AS amt,
    sum(amt) OVER (PARTITION BY account ORDER BY (year_month || '/01')::date
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rollover
   FROM table.sample_data
    WHERE type <> 'DZ' AND ((year_month || '/01')::date >= (date_trunc('month', current_date) - interval '1 year'))
    GROUP BY amt, type, account, year_month
    ORDER BY year_month DESC
)
SELECT file_date,
    SUM(amt) AS amt,
    SUM(rollover) AS rollover
    FROM temp 
        GROUP BY file_date
        ORDER BY file_date DESC
0

Will this be enough for you?

SELECT
  b.file_date,
  SUM(a.amt) AS amt,
  SUM(SUM(a.amt)) OVER(ORDER BY b.file_date
                       ROWS UNBOUNDED PRECEDING) AS rollover
FROM sample_data AS a
INNER JOIN LATERAL (SELECT TO_DATE(a.year_month, 'yyyy/mm') AS file_date) AS b
  ON TRUE
WHERE
  a."type" <> 'DZ' AND
  b.file_date >= DATE_TRUNC('MONTH', CURRENT_DATE) - INTERVAL '1 YEAR 6 MONTHS'
GROUP BY b.file_date;

Results:

+------------+------------+------------+
| file_date  |    amt     |  rollover  |
+------------+------------+------------+
| 2019-01-01 | 75008.00   | 75008.00   |
| 2019-02-01 | 5008.00    | 80016.00   |
| 2019-03-01 | 93745.00   | 173761.00  |
| 2019-04-01 | 3745.00    | 177506.00  |
| 2019-05-01 | 2434047.00 | 2611553.00 |
| 2019-06-01 | 422922.15  | 3034475.15 |
+------------+------------+------------+

Demo.

| improve this answer | |
  • Yes, it work perfectly. Thanks so much – i2cute Mar 9 at 3:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.