Results 1 to 2 of 2
  1. #1
    Join Date
    May 2014
    Posts
    2

    Unanswered: Column is a Cumulative sum

    I have a financial db in which transactions from all acounts exist. I want to make individual views representing a particular account (easy enough). I want to add a computed column in this view as well. The balance. I have a debit and a credit column.
    I have tried

    Code:
    CREATE OR REPLACE VIEW e53 AS 
     SELECT financial."timestamp" AS "Date",
        financial.description AS "Description",
        financial.debit AS "Debit",
        financial.credit AS "Credit",
        COALESCE(financial.credit, 0.0::double precision) - COALESCE(financial.debit, 0.0::double precision) + COALESCE(lag(financial.credit, 1) OVER (ORDER BY financial."timestamp"), 0.0::double precision) - COALESCE(lag(financial.debit, 1) OVER (ORDER BY financial."timestamp"), 0.0::double precision) AS "Balance",
        financial.notes AS "Notes",
        financial.descretionary AS "Discretionary",
        financial.num,
        financial.cleared AS "Cleared",
        financial.repay AS "Repay",
        financial.category AS "Category",
        financial.image AS "Image Link",
        financial.tags AS "Tags",
        financial.unfinished AS "Unfinished"
       FROM financial
      WHERE financial.account = 'e53'::bpchar
      ORDER BY financial."timestamp";
    But this (as you'd expect) only sums the the current (credit - debit) and the previous (credit - debit). Anyone have any ideas?

  2. #2
    Join Date
    May 2014
    Posts
    2

    Column in view as cumulative sum

    I have a financial db in which transactions from all acounts exist. I want to make individual views representing a particular account (easy enough). I want to add a computed column in this view as well. The balance. I have a debit and a credit column.
    I have tried

    Code:
    CREATE OR REPLACE VIEW e53 AS 
     SELECT financial."timestamp" AS "Date",
        financial.description AS "Description",
        financial.debit AS "Debit",
        financial.credit AS "Credit",
        COALESCE(financial.credit, 0.0::double precision) - COALESCE(financial.debit, 0.0::double precision) + COALESCE(lag(financial.credit, 1) OVER (ORDER BY financial."timestamp"), 0.0::double precision) - COALESCE(lag(financial.debit, 1) OVER (ORDER BY financial."timestamp"), 0.0::double precision) AS "Balance",
        financial.notes AS "Notes",
        financial.descretionary AS "Discretionary",
        financial.num,
        financial.cleared AS "Cleared",
        financial.repay AS "Repay",
        financial.category AS "Category",
        financial.image AS "Image Link",
        financial.tags AS "Tags",
        financial.unfinished AS "Unfinished"
       FROM financial
      WHERE financial.account = 'e53'::bpchar
      ORDER BY financial."timestamp";
    But this (as you'd expect) only sums the the current (credit - debit) and the previous (credit - debit). Anyone have any ideas?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •