CODEWITHSUNDEEP
sqlpostgresql
avi
avi

Reputation: 1846

How to conditional sum two columns in PostgreSQL 9.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.

Upvotes: 3

Views: 1200

Answers (3)

joanolo
joanolo

Reputation: 6328

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.

Upvotes: 3

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

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

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 3

Related Questions