Add running or cumulative total

Question

I have below query which gives me expected results:

SELECT 
  total_orders,
  quantity,
  available_store_credits
FROM
  (
  SELECT
    COUNT(orders.id) as total_orders,
    date_trunc('year', confirmed_at) as year,
    date_trunc('month', confirmed_at) as month,
    SUM( quantity ) as quantity,
  FROM
    orders
    INNER JOIN (
      SELECT
        orders.id,
        sum(quantity) as quantity
      FROM
        orders
        INNER JOIN line_items ON line_items.order_id = orders.id
      WHERE
        orders.deleted_at IS NULL
        AND orders.status IN (
          'paid', 'packed', 'in_transit', 'delivered'
        )
      GROUP BY
        orders.id
    ) as order_quantity
      ON order_quantity.id = orders.id
  GROUP BY month, year) as orders_transactions
      
  FULL OUTER JOIN
  (
    SELECT
      date_trunc('year', created_at) as year,
      date_trunc('month', created_at) as month,
      SUM( ROUND( ( CASE WHEN amount_in_cents > 0 THEN amount_in_cents end) / 100, 2 )) AS store_credit_given,
      SUM( ROUND( amount_in_cents / 100, 2 )) AS available_store_credits
    FROM
      store_credit_transactions
    GROUP BY month, year
  ) as store_credit_results
    ON orders_transactions.month = store_credit_results.month

I want to add one more column beside available_store_credits which will calculate running total of available_store_credits.
These are my trials, but none are working:

Attempt #1

SELECT
  total_orders,
  quantity,
  available_store_credits,
  cum_amt
FROM
  (
  SELECT
    COUNT(orders.id) as total_orders,
    date_trunc('year', confirmed_at) as year,
    date_trunc('month', confirmed_at) as month,
    SUM( quantity ) as quantity,
  FROM
    orders
    INNER JOIN (
      SELECT
        orders.id,
        sum(quantity) as quantity
      FROM
        orders
        INNER JOIN line_items ON line_items.order_id = orders.id
      WHERE
        orders.deleted_at IS NULL
        AND orders.status IN (
          'paid', 'packed', 'in_transit', 'delivered'
        )
      GROUP BY
        orders.id
    ) as order_quantity
      ON order_quantity.id = orders.id
  GROUP BY month, year) as orders_transactions
      
  FULL OUTER JOIN
  (
    SELECT
      date_trunc('year', created_at) as year,
      date_trunc('month', created_at) as month,
      SUM( ROUND( ( CASE WHEN amount_in_cents > 0 THEN amount_in_cents end) / 100, 2 )) AS store_credit_given,
      SUM( ROUND( amount_in_cents / 100, 2 )) AS available_store_credits
      SUM( amount_in_cents ) OVER (ORDER BY date_trunc('month', created_at), date_trunc('year', created_at)) AS cum_amt
    FROM
      store_credit_transactions
    GROUP BY month, year
  ) as store_credit_results
    ON orders_transactions.month = store_credit_results.month

Attempt #2

SELECT 
  total_orders,
  quantity,
  available_store_credits,
  running_tot
FROM
  (
  SELECT
    COUNT(orders.id) as total_orders,
    date_trunc('year', confirmed_at) as year,
    date_trunc('month', confirmed_at) as month,
  FROM
    orders
    INNER JOIN (
      SELECT
        orders.id,
        sum(quantity) as quantity
      FROM
        orders
        INNER JOIN line_items ON line_items.order_id = orders.id
      WHERE
        orders.deleted_at IS NULL
        AND orders.status IN (
          'paid', 'packed', 'in_transit', 'delivered'
        )
      GROUP BY
        orders.id
    ) as order_quantity
      ON order_quantity.id = orders.id
  GROUP BY month, year) as orders_transactions
      
  FULL OUTER JOIN
  (
    SELECT
      date_trunc('year', created_at) as year,
      date_trunc('month', created_at) as month,
      SUM( ROUND( amount_in_cents / 100, 2 )) AS available_store_credits,
      SUM (available_store_creds) as running_tot
    FROM
      store_credit_transactions
      INNER JOIN (
        SELECT t0.id,
            (
             SELECT SUM( ROUND( amount_in_cents / 100, 2 )) as running_total
             FROM store_credit_transactions as t1 
             WHERE date_trunc('month', t1.created_at) <= date_trunc('month', t0.created_at)
            ) AS available_store_creds
            FROM store_credit_transactions AS t0
          
      ) as results
      ON results.id = store_credit_transactions.id
    GROUP BY month, year
  ) as store_credit_results
    ON orders_transactions.month = store_credit_results.month

Erwin Brandstetter · Answer 1 · 2021-10-23T22:34:38.913

Making some assumptions about the undisclosed table definition and Postgres version (assuming current Postgres 14), this should do it:

SELECT total_orders, quantity, available_store_credits
     , sum(available_store_credits) OVER (ORDER BY month) AS cum_amt  -- HERE!!
FROM  (
   SELECT date_trunc('month', confirmed_at) AS month
        , count(*) AS total_orders
        , sum(quantity) AS quantity
   FROM  (
      SELECT o.id, o.confirmed_at, sum(quantity) AS quantity
      FROM   orders     o
      JOIN   line_items l ON l.order_id = o.id
      WHERE  o.deleted_at IS NULL
      AND    o.status IN ('paid', 'packed', 'in_transit', 'delivered')
      GROUP  BY 1
      ) o
   GROUP  BY 1
   ) orders_transactions
      
FULL   JOIN (
   SELECT date_trunc('month', created_at) AS month
        , round(sum(amount_in_cents) FILTER (WHERE amount_in_cents > 0) / 100, 2) AS store_credit_given
        , round(sum(amount_in_cents) / 100, 2) AS available_store_credits
   FROM   store_credit_transactions
   GROUP  BY 1
   ) store_credit_results USING (month)

Assuming you want the running sum to show up in every row and order of the date.

First, I simplified and removed some cruft:

date_trunc('year', confirmed_at) as year, was 100 % redundant noise in your query. I removed it.
As was another join to orders. Removed that, too. Assuming orders.id is defined as PK, we can further simplify. See:
- PostgreSQL - GROUP BY clause

Use the superior aggregate FILTER. See:

Aggregate columns with additional (distinct) filters

Simplified a couple of other minor bits.

Add running or cumulative total

Attempt #1

Attempt #2

1 Answers1