CODEWITHSUNDEEP
sql
FrenchConnections
FrenchConnections

Reputation: 391

Find the difference between dates between rows with the same values

I have a dataset that includes similar values and a number of dates. I am trying to write a sql query to find the difference in dates between rows and sum the values in another row. The data set looks like this:

id         |  date          | value |  
 1            2015-06-01      10
 1            2015-09-22      25
 2            2015-12-10      15
 2            2015-07-11      20
 2            2015-10-18      25
 3            2015-04-05      30
 3            2015-05-02      45
 4            2015-06-01      20

And what I am trying to get to is this:

id   |  date_diff |  value
1       42           35
2       149          60
3       27           75
4       0            20

The idea is the date_diff finds the difference between the dates for each id, and sums the value. However, the date_diff function isn't working for me, and I think this may be one of the first issues. The date_diff function is returning this error:

function datediff(unknown, date, timestamp without time zone) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I'm using a public data set, so that might be part of the issue. Any help or ideas would be great!

Upvotes: 1

Views: 61

Answers (1)

tmaj
tmaj

Reputation: 35027

Let's create a test table:

create table test 
(
id int ,
date DATE,
value int
)


insert into test values (1, CAST('2015-06-01' AS DATE), 10); 
insert into test values (1, CAST('2015-09-22' AS DATE), 25); 
insert into test values (2, CAST('2015-12-10' AS DATE), 100); 
insert into test values (2, CAST('2015-07-11' AS DATE), 200);

Let's look at the data:

select * from test ORDER BY id;

1   2015-06-01  10
1   2015-09-22  25
2   2015-12-10  100
2   2015-07-11  200

Let's do the magic. 

 select datediff(day, MIN(DATE), MAX(DATE)) as diff_in_days, sum(value) as sum_of_values FROM test group by id;

113 35
152 300

Please note that you didn't specify what should happen when there are 3 rows with id. The code will still work, but if it would make sense from the logical point of view for your application, I don't know.

Upvotes: 1

Related Questions