How to conditional sum two columns in PostgreSQL 9.3

Question

I have the following data table:

month      marketid   totalsold   totalshipped   lefttoship_thismonth
....
01-01-2015   1          100            50              50
01-01-2015   2          10             3               7
01-01-2015   3          0              0               0
01-02-2015   1          0              50             -50
01-02-2015   2          20             0               20
01-02-2015   3          0              0                0

Basically this table shows information about orders and shipments per market per month. The date 01-01-2015 in month column actually represents Jan 2015 (the whole month).

I want to SUM the lefttoship_thismonth per market for each month with all previous months. This is needed as someone can place order in January which was supplied in February. So I want to know how many items I still need to ship per month.

The output should be:

month      marketid   totalsold   totalshipped   totallefttoship   TOTALLEFT
01-01-2015   1          100            50              50             50   
01-01-2015   2          10             3               7               7
01-01-2015   3          0              0               0              0
01-02-2015   1          0              50             -50             0    /50-50
01-02-2015   2          20             0               20             27  /7+20
01-02-2015   3          0              0                0               0  / 0+0

How can I do that? I have no idea how to sum this way and the month column is very hard to work with.

Pரதீப் · Answer 1 · 2017-01-15T12:55:17.510

3

Sum()Over() window aggregate function

SELECT "month",
       marketid,
       totalsold,
       totalshipped,
       lefttoship_thismonth,
       Sum(lefttoship_thismonth)OVER(partition BY marketid ORDER BY month ) AS TOTALLEFT
FROM   yourtable
ORDER  BY "month",
          marketid

Live Demo

edited Jan 15 '17 at 12:55

answered Jan 15 '17 at 12:43

Pரதீப்

91,748
19
131
172

1

Beat me by a little bit. :) +1 – Gurwinder Singh Jan 15 '17 at 12:44
@avi what is the type of your `month` column? – Gurwinder Singh Jan 15 '17 at 12:52
@avi - Added demo as well. As Gurv asked what is the datatype of `Month` column – Pரதீப் Jan 15 '17 at 12:55
If it is varchar, you can try `to_date(month,'dd-mm-yyyy')` instead of `month` in the `order by` – Gurwinder Singh Jan 15 '17 at 12:57
@Prdp month column is of type DATE – avi Jan 15 '17 at 13:11
1

@avi - Added demo as well. Check it out it gives expected result – Pரதீப் Jan 15 '17 at 13:19

joanolo · Accepted Answer · 2017-01-15T18:27:23.197

You can do it with a SubQuery, if your versions of PostgreSQL does not (yet) allow for window functions):

WITH t (month, marketid, totalsold, totalshipped, lefttoship_thismonth) AS
(VALUES
    ('01-01-2015'::date,   1, 100, 50,  50),
    ('01-01-2015'::date,   2,  10,  3,   7),
    ('01-01-2015'::date,   3,   0,  0,   0),
    ('01-02-2015'::date,   1,   0, 50, -50),
    ('01-02-2015'::date,   2,  20,  0,  20),
    ('01-02-2015'::date,   3,   0,  0,   0) 
)

SELECT
    month, 
    marketid, 
    totalsold, 
    totalshipped, 
    lefttoship_thismonth, 
    (SELECT sum(lefttoship_thismonth) 
       FROM t t2 
      WHERE t2.marketid  = t1.marketid AND
            t2.month    <= t1.month
    ) AS total_left
FROM 
    t t1
ORDER BY
    month, marketid ;

would get you the following result:

|------------+----------+-----------+--------------+----------------------+------------|
|   month    | marketid | totalsold | totalshipped | lefttoship_thismonth | total_left |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-01 |    1     |    100    |      50      |          50          |     50     |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-01 |    2     |    10     |      3       |          7           |     7      |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-01 |    3     |     0     |      0       |          0           |     0      |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-02 |    1     |     0     |      50      |         -50          |     0      |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-02 |    2     |    20     |      0       |          20          |     27     |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-02 |    3     |     0     |      0       |          0           |     0      |
|------------+----------+-----------+--------------+----------------------+------------|

If you can use Window functions (which are more efficient), you can do the following:

SELECT
    month, 
    marketid, 
    totalsold, 
    totalshipped, 
    lefttoship_thismonth, 
    ( sum(lefttoship_thismonth) 
           OVER (PARTITION BY marketid ORDER BY month ROWS UNBOUNDED PRECEDING)
    ) AS total_left
FROM 
    t t1
ORDER BY
    month, marketid ;

If your month column is a varchar (not a good idea), you can cast it to date, or use the to_date function.

score 2 · Answer 3 · answered Jan 15 '17 at 12:58

Too long for a comment.

If the column is varchar type then you'll have to convert it into date to use it in the order by clause like this.

select t.*,
  sum(totallefttoship) over 
   (partition by marketid order by to_date(month,'dd-mm-yyyy')) as TOTALLEFT
From yourtable t

How to conditional sum two columns in PostgreSQL 9.3

3 Answers3

Linked