How to write sql query that sums up values by number of days?-CodePudding

I have a table which looks like this:

start_date    end_date       id   value
05.10.2010    07.10.2010      1    5
11.12.2010    15.12.2010      2    10
01.01.2023                    3    6

I want to write sql query that will multiply number of days from start_date to end_date for each id with its value. So desired result is:

id  sum_value
1    15
2    50
3    60

its 15 because there are 3 days (from 05.10.2010 to 07.10.2010) for id 1 and value is 5

its 50 because there are 5 days (from 11.12.2010 to 15.12.2010) for id 2 and value is 10

its 60 because there are 10 days (from 01.01.2023 to current date) for id 3 and value is 6 if end_date is empty it means its current date

How to do that?

CodePudding user response：

Use DATEDIFF() to subtract the dates. Add 1 to that because it doesn't include both ends.

Use IFNULL() to replace the missing end_date with the current date.

SELECT id, value * (1   (datediff(IFNULL(end_date, CURDATE()), start_date)) AS sum_value
FROM yourtable