Subtracting two columns with the same month in a table-CodePudding

I am using PostgreSQL and what I want to achieve is I want to be able to subtract value1 from value2 where the month is the same and create a new column called value3. The image below is how my DB table looks like

Sample data:

| value1   | value2 | month                  | 
| -------- | ------ | -----------------------|
| 268154.33| 0      | 2021-08-01 00:00:00 00 |
| 0        | 14075  | 2021-08-01 00:00:00 00 |
| 300703   | 0      | 2021-09-01 00:00:00 00 |
| 0        | 189130 | 2021-09-01 00:00:00 00 |
| 16660.65 | 0      | 2021-10-01 00:00:00 00 |
| 0        | 17930  | 2021-10-01 00:00:00 00 |
| 8200     | 0      | 2020-10-01 00:00:00 00 |
| 33200    | 0      | 2020-11-01 00:00:00 00 |
| 30848    | 0      | 2021-02-01 00:00:00 00 |

So the Result should be thus:

| value1   | value2 | month                  | value3   |
| -------- | ------ | -----------------------|----------|
| 268154.33| 14075  | 2021-08-01 00:00:00 00 | 254079.33|
| 300703   | 189130 | 2021-09-01 00:00:00 00 | 111573   |
| 16660.65 | 17930  | 2021-10-01 00:00:00 00 | -1269.35 |
| 8200     | 0      | 2020-10-01 00:00:00 00 | 8200     |
| 33200    | 0      | 2020-11-01 00:00:00 00 | 33200    |
| 30848    | 0      | 2021-02-01 00:00:00 00 | 30848    |

Table Image

CodePudding user response：

I presume that you are looking for a "self-join", something like this:

with cte (value1, value2, mth)as 
    (Values
        (8200.0, 0, '2020-10-01'::date),
        (33200.0, 0, '2020-11-01'::date),
        (30848.0, 0, '2021-02-01'::date),
        (220381.66, 0, '2021-07-01'::date),
        (211909.16, 0, '2021-05-01'::date),
        (13298.0, 0, '2020-12-01'::date),
        (174049.16, 0, '2021-04-01'::date),
        (203057.5, 0, '2021-03-01'::date),
        (290964.66, 0, '2021-06-01'::date),
        (300703.0, 0, '2021-09-01'::date),
        (21348.0, 0, '2021-01-01'::date),
        (16660.65, 0, '2021-10-01'::date),
        (268154.33, 0, '2021-08-01'::date),
        (0.0, 181930, '2021-09-01'::date),
        (0.0, 14075, '2021-08-01'::date),
        (0.0, 17930, '2021-10-01'::date)
    )
SELECT c1.*, c1.value1 - COALESCE(c2.value2, 0) as value3 
FROM cte c1 
LEFT OUTER JOIN cte c2 ON c1.mth = c2.mth AND c2.value2 > 0
WHERE c1.value1 > 0;

Please note that this gives more values than in your result set, but I suspect this was just laziness on your part, otherwise I could see no logic why certain results were excluded.

By way of explanation in this case the self-join is an outer join, because whilst all months have a value1 not all have a value2. Secondly the requirement for the right hand side of the join (c2) to have value2 > 0 is placed in the join, not as a where. If you were to put it in the where it effectively promotes the join to an inner join, and you would only get three results.

Finally please observe in my answer, how I included the sample data as text with a values keyword. In future questions please do something similar. The answerers here are volunteers, and the more you can save them time (by reducing their need to type), the better your questions will be received and answered.

CodePudding user response：

I think the below query can give the faster result as this scans the table only once-

SELECT SUM(value1), SUM(c1.value2), mth, SUM(c1.value1) - SUM(c1.value2) value3 
FROM cte
GROUP BY mth
ORDER BY mth;

Demo.