3

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.

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

6 Comments

Beat me by a little bit. :) +1
@avi what is the type of your month column?
@avi - Added demo as well. As Gurv asked what is the datatype of Month column
If it is varchar, you can try to_date(month,'dd-mm-yyyy') instead of month in the order by
@avi - Added demo as well. Check it out it gives expected result
|
2

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.