Mysql – Iterating through results and performing sum on values

datejoin;MySQLsum

I'm using MySQL and not really sure where to begin with this. I've got a query which returns N dates for a given company (N is likely to be 10 but could be more/less). These dates are returned as such (ddmmyyyy):
SELECT id, dates FROM myTable WHERE id='123'

123,01012016

123,13012016

etc…

I'd like to find information from another table that hs similar dates and get values within a boundary. For example, Table2 has

ID, DATE, AGE
123,01012016, 3 
123,03012016, 7
123,15012016, 10

I'd like to SUM all Ages between each date brought back by the first command. In this example, the first command boundary would be 01/01/2016 – 13/01/2016 so I'd like to sum all values in the second table that fall between that bounday. This would loop for all date boundaries returned in the first query.

I don't really have a starting point for this type of query in MySQL so any help or pointers in what I should be reading docs for is appreciated

Thanks

Best Answer

I can give you some guidance, at a minimum. I don't use MySQL in detail, but in general:

Step 1: get the date ranges

I assume that for each row returned from mytable that you want the date in that row as a start date, and the date in the following row as the end date. Check MySQL documentation on "windowing functions" (related to aggregate functions). There may be a function that can do this for you (in SQL Server, the function is LEAD. If you can't find that, you can always do a subquery in each row, returning the smallest date (matching the same criteria) bigger than the date in the current row. This would look something like:

SELECT id, dates as startdate
      ,(SELECT MIN(dates) FROM mytable WHERE id = a.id and dates > a.dates) as enddate
  FROM mytable a
 WHERE id = '123'

(Exact syntax may be different).

Step 2: Get the SUMs

Given that we have the start and end dates for each range, getting the SUM of the age ranges is fairly easy. We join the query we did above against Table2 and use GROUP BY and SUM. I assume that dates that fall on a boundary should not be included in both groups, so we'll toss them into the later group:

SELECT dtr.startdate, dtr.enddate, SUM(ag.age) as sum_Age
  FROM (SELECT id, dates as startdate
              ,(SELECT MIN(dates) FROM mytable WHERE id = a.id and dates > a.dates) as enddate
          FROM mytable a
         WHERE id = '123'
       ) dtr
       LEFT OUTER JOIN Table2 as ag ON (ag.date >= dtr.startdate AND ag.date < dtr.enddate)
 GROUP BY dtr.startdate, dtr.enddate;

(Again, syntax may need adjusting for MySQL). Note that this does not sum up ages for any dates either before the first date, or on or after the last date, as those are not part of valid ranges.

Hope this helps point you in the right direction.

Related Question