Sum according to dates in a single query in PostgreSql

Question

I have a table like below:

ID | Date       | Value
---+------------+------
H1 | 11/9/2015  | 3
H2 | 11/10/2015 | 5
H3 | 11/11/2015 | 2
H1 | 11/9/2015  | 1
H2 | 11/13/2015 | 4
H3 | 11/8/2015  | 5
H1 | 11/15/2015 | 8

I want to create a single query which can show me the result as below:

ID | All Values | Future Values
---+------------+--------------
H1 |         12 |             8
H2 |          9 |             9
H3 |          7 |             2

I am able to sum all the data by writing query

select ID, Sum(ID) from table group by ID

but how to sum future data in same query? I managed to create a separate query for future data:

select ID, sum(ID) from table where date > now() group by ID

Please help me out to make a single query... Thanks.

fthiella · Accepted Answer · 2015-11-09 10:28:02Z

1

You could use CASE WHEN:

SELECT
  ID,
  SUM(value) AS all_values,
  SUM(CASE WHEN date > current_date THEN value END) AS future_values
FROM
  table
GROUP BY
  ID

(I'm using current_date because I think that your date column contains just date information with no time, use now() if this is not the case)

answered Nov 9, 2015 at 10:28

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Collectives™ on Stack Overflow

Sum according to dates in a single query in PostgreSql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related