Group by Alternative to the Calender Year...

[edoggie]

I need to create a query such as the following:

SELECT 
SUBSTRING(contributions.apply_date,1,4) as year_made, 
SUM(amount) AS year_total
FROM contributions
WHERE contributions.contributed_for = {$user_id}
AND apply_date >= '{$start_date}' AND apply_date <= '{$end_date}'
GROUP BY year_made
ORDER BY year_made DESC

 

but instead of Grouping the data by the Substring year_made I want to group the data from an arbitrary date range, like 3-01-2007 to 2-31-2008 and then by 3-01-2008 to 2-31-2009 etc. 

 

So each GROUP BY result would have the SUM(amount) AS year_total be equal to the total contributions_for for the given range? 

 

I don't know exactly how I would do this, other then creating a PHP loop and create each Summary via seperate MySQL call.  Is there anyone that can build this functionality entirely in MySQL.    Seems to be like it should be possible somehow?

[Barand]

One way

 

My data file

mysql> SELECT * FROM eventdates e;
+---------+---------------------+-------+
| eventid | eventdate           | price |
+---------+---------------------+-------+
|       1 | 2007-09-06 00:00:00 | 15.00 |
|       1 | 2007-09-07 00:00:00 | 12.00 |
|       2 | 2007-09-08 00:00:00 |  5.00 |
|       3 | 2007-09-20 00:00:00 | 10.00 |
+---------+---------------------+-------+

 

Now a file to define the date groups

mysql> SELECT * FROM eventdategroup;
+----+------------+------------+
| id | startdate  | enddate    |
+----+------------+------------+
|  1 | 2007-09-01 | 2007-09-08 |
|  2 | 2007-09-08 | 2007-09-21 |
+----+------------+------------+

 

The query

mysql> SELECT g.id,g.startdate,g.enddate, SUM(e.price) as total
    -> FROM eventdates e
    -> INNER JOIN eventdategroup g ON e.eventdate BETWEEN g.startdate AND g.enddate
    -> GROUP BY g.id;
+----+------------+------------+-------+
| id | startdate  | enddate    | total |
+----+------------+------------+-------+
|  1 | 2007-09-01 | 2007-09-08 | 27.00 |
|  2 | 2007-09-08 | 2007-09-21 | 15.00 |
+----+------------+------------+-------+

[edoggie]

Thats a nice example, but I'm afraid it wouldn't work within my application.   Each participant might have a different "Enrollment Date" for the program, so they would have a different Start and End date.   The grouping are of arbitrary length, for each year within the plan.  So I would have too many tables I would have to come up with and too many additional tables to join. 

 

Anyone have any other suggestions or improvements to Barand's design so that I could accomplish the task without so many additional tables being generated?

[fenway]

You can arbitrarily group things however you'd like, as long as you can come up with an expression that assigns each group a different value.