CODEWITHSUNDEEP
postgresql
Chris
Chris

Reputation: 365

I have a query that groups usage by user by day how would I add a running total to this query?

I have the following query:

SELECT
usersq1.id AS user_id, name, completed_at, 
COUNT(usersq1.id) AS trips,
SUM(cost_amount_cents) AS daily_cost_amount_cents
FROM usersq1
LEFT OUTER JOIN tripsq1
ON usersq1.id = user_id
GROUP by usersq1.id, name, completed_at
ORDER by user_id, name, completed_at;

Which returns the following:

 user_id |        name         | completed_at | trips | daily_cost_amount_cents
---------+---------------------+--------------+-------+-------------------------
    1001 | Makeda Mosser       | 2017-06-01   |     2 |                     125
    1001 | Makeda Mosser       | 2017-06-02   |     1 |                     125
    1001 | Makeda Mosser       | 2017-06-03   |     2 |                     350
    1001 | Makeda Mosser       | 2017-06-04   |     2 |                     200
    1001 | Makeda Mosser       | 2017-06-06   |     1 |                     100
    1001 | Makeda Mosser       | 2017-06-07   |     1 |                     125
    1001 | Makeda Mosser       | 2017-06-08   |     1 |                     150
    1002 | Libbie Luby         | 2017-06-02   |     2 |                     125
    1002 | Libbie Luby         | 2017-06-09   |     1 |                     175
    1003 | Linn Loughran       | 2017-06-03   |     1 |                      75
    1004 | Natacha Ned         | 2017-06-04   |     1 |                     100
    1005 | Lorrine Lunt        | 2017-06-05   |     1 |                     125
    1006 | Tami Tineo          | 2017-10-06   |     1 |                     150
    1007 | Delisa Deen         | 2017-10-07   |     1 |                     175
    1008 | Mimi Miltenberger   | 2017-10-08   |     1 |                     200
    1009 | Seth Sneller        | 2017-10-09   |     1 |                      25
    1010 | Rickie Rossi        | 2017-10-10   |     1 |                      50
    1011 | Jenise Jeanbaptiste | 2017-06-01   |     1 |                     200
    1011 | Jenise Jeanbaptiste | 2017-07-01   |     1 |                      75
    1012 | Genia Glatz         | 2017-06-02   |     1 |                      25
    1012 | Genia Glatz         | 2017-07-02   |     1 |                      50
    1013 | Onita Oddo          | 2017-06-03   |     1 |                      50
    1014 | Dario Dreyer        | 2017-06-04   |     1 |                      75
    1014 | Dario Dreyer        | 2017-06-24   |     5 |                     750
    1015 | Toby Trent          |              |     1 |

I would like to produce another cumulative sum column which keeps a running total of daily_cost_amount_cents per user. The expected outlook I would like is something like this:

+---------+---------------------+------------+-------+-------------------------+-----------+
| user_id |        name         | created_at | trips | daily_cost_amount_cents | cum_cents |
+---------+---------------------+------------+-------+-------------------------+-----------+
|    1001 | Makeda Mosser       | 6/1/17     |     2 |                     125 |       125 |
|    1001 | Makeda Mosser       | 6/2/17     |     1 |                     125 |       250 |
|    1001 | Makeda Mosser       | 6/3/17     |     2 |                     350 |       600 |
|    1001 | Makeda Mosser       | 6/4/17     |     2 |                     200 |       800 |
|    1001 | Makeda Mosser       | 6/6/17     |     1 |                     100 |       900 |
|    1001 | Makeda Mosser       | 6/7/17     |     1 |                     125 |      1025 |
|    1001 | Makeda Mosser       | 6/8/17     |     1 |                     150 |      1175 |
|    1002 | Libbie Luby         | 6/2/17     |     2 |                     125 |       125 |
|    1002 | Libbie Luby         | 6/9/17     |     1 |                     175 |       300 |
|    1003 | Linn Loughran       | 6/3/17     |     1 |                      75 |        75 |
|    1004 | Natacha Ned         | 6/4/17     |     1 |                     100 |       100 |
|    1005 | Lorrine Lunt        | 6/5/17     |     1 |                     125 |       125 |
|    1006 | Tami Tineo          | 10/6/17    |     1 |                     150 |       150 |
|    1007 | Delisa Deen         | 10/7/17    |     1 |                     175 |       175 |
|    1008 | Mimi Miltenberger   | 10/8/17    |     1 |                     200 |       200 |
|    1009 | Seth Sneller        | 10/9/17    |     1 |                      25 |        25 |
|    1010 | Rickie Rossi        | 10/10/17   |     1 |                      50 |        50 |
|    1011 | Jenise Jeanbaptiste | 6/1/17     |     1 |                     200 |       200 |
|    1011 | Jenise Jeanbaptiste | 7/1/17     |     1 |                      75 |       275 |
|    1012 | Genia Glatz         | 6/2/17     |     1 |                      25 |        25 |
|    1012 | Genia Glatz         | 7/2/17     |     1 |                      50 |        75 |
|    1013 | Onita Oddo          | 6/3/17     |     1 |                      50 |        50 |
|    1014 | Dario Dreyer        | 6/4/17     |     1 |                      75 |        75 |
|    1014 | Dario Dreyer        | 6/24/17    |     5 |                     750 |       750 |
|    1015 | Toby Trent          |            |     0 |                         |           |
+---------+---------------------+------------+-------+-------------------------+-----------+

I am pretty sure that I need to use a window function to do this but can't seem to do it while preserving the grouping by user_id and created_by

Upvotes: 0

Views: 30

Answers (1)

gwaigh
gwaigh

Reputation: 1302

The problem is that in the presence of a GROUP BY clause, the window functions iterate over each group rather than multiple grouped rows. Put your query into a WITH clause and you can easily do the windowing you want:

WITH t AS (
           SELECT usersq1.id AS user_id,
                  name,
                  completed_at, 
                  COUNT(completed_at) AS trips, -- To correctly handle 0 trips
                  SUM(cost_amount_cents) AS daily_cost_amount_cents
             FROM usersq1
  LEFT OUTER JOIN tripsq1 ON usersq1.id = user_id
         GROUP BY usersq1.id, name, completed_at
         ORDER BY user_id, name, completed_at
) SELECT user_id,
         name,
         completed_at AS created_at,
         trips,
         daily_cost_amount_cents,
         SUM(daily_cost_amount_cents) OVER (PARTITION BY user_id
                                            ORDER BY user_id, completed_at)
    FROM t;

Upvotes: 1

Related Questions