MySQL Query To Produce Amount Per Month

Question

I have a table that contains:

id           date                   user_id   duration  amount
1            2014-01-01 00:00:00    1         1         £10
2            2014-01-02 00:00:00    2         2         £10
3            2014-01-03 00:00:00    3         3         £10

I'm trying to display the amount per month. Any ideas how to do this in a query?

Answer 1

Working on the assumptions that you can extract the month from you datetime easily, so the real question is about the aggregation logic, and that you can create a numbers table.

Here is a simple example that shows the pattern.

sqlfiddle

CREATE TABLE Num (num int);
INSERT INTO Num VALUES (0),(1),(2),(3),(4);

CREATE TABLE Tbl (start int, run int);
INSERT INTO Tbl VALUES (1,2),(2,3);

SELECT start + num active_month
      ,count(*) * 10 income
  FROM Tbl
       INNER JOIN
       Num ON num < run
GROUP BY start + num

Answer 2

What about :

SELECT a.my_date, a.income, IFNULL(SUM(DISTINCT(a.income)) + sum( b.income ), a.income) as roll_up
FROM (
SELECT purchase_id, DATE_FORMAT( date_purchased, '%y-%m') AS my_date, SUM( amount_income / duration ) AS "income"
FROM incomes
GROUP BY my_date
) AS a
LEFT OUTER JOIN (
SELECT purchase_id, DATE_FORMAT( date_purchased, '%y-%m') AS my_date, SUM(amount_income / duration ) AS "income"
FROM incomes
GROUP BY my_date
) AS b ON ( a.purchase_id > b.purchase_id )

GROUP BY a.purchase_id

It's a bit tricky to do that in one shot - and it might be improved - but that gives the following results :

my_date  income     roll_up
13-12    8.5000     8.5000
14-01   10.0000     18.5000
14-02   16.6667     35.1667

My data set is :

1   2013-12-28 00:00:00     1   2   15
2   2014-01-04 00:00:00     2   1   10
3   2014-02-04 00:00:00     3   6   40
4   2013-12-29 00:00:00     4   1   1
5   2014-02-28 00:00:00     5   2   20

Answer 3

Like Karl, I'm pretty sure some kind of numbers table is necessary here. Personally I like the approach given here, which defines a view (well, several of them) to generate numbers, instead of having to actually store a table full of numbers. Whether you use a table or a view, when you SELECT from it, it just looks like this:

 n
---
 0
 1
 2
 3
 …

With that you can construct a query like this:

SELECT
  purchases.purchase_id,
  purchases.date_purchased,
  purchases.duration,
  -- generator_16 is our numbers table
  generator_16.n,
  -- Below we calculate the year and month (year_mon) in the following way:
  -- (1) Get the first day of the year, e.g. if date_purchased is 2012-12-28,
  --     this gives us 2012-01-01.
  -- (2) Get the month number, e.g. 12 for 2012-12-28) and add that many months
  --     to the first day of the year, which gives us the first day of the
  --     month, 2012-12-01.
  -- (3) Add "n" months, where "n" is the number we get from the numbers table,
  --     starting at 0.
  DATE_ADD(                                              -- (3)
    DATE_ADD(                                            -- (2)
      MAKEDATE( YEAR(purchases.date_purchased), 1 ),     -- (1)
      INTERVAL MONTH(purchases.date_purchased) - 1 MONTH -- (2)
    ),
    INTERVAL generator_16.n MONTH                        -- (3)
  ) AS year_mon,
  purchases.amount_income / purchases.duration AS amount
FROM purchases
-- The below JOIN means that if `purchases.duration` is 3, we get three rows
-- that have 0, 1, and 2 in the `n` column, which we use as the number of dates
-- to add in (3) above.
JOIN generator_16
ON generator_16.n BETWEEN 0 AND purchases.duration - 1
ORDER BY purchases.purchase_id, year_mon;

This gives us a result like this (SQL Fiddle):

purchase_id  date_purchased  duration  n      year_mon  amount
-----------  --------------  --------  -  ------------  ------
          1    2013-12-28 …         2  0  2013-12-01 …     7.5
          1    2013-12-28 …         2  1  2014-01-01 …     7.5

          2    2014-01-04 …         1  0  2014-01-01 …      10

          3    2014-02-04 …         6  0  2014-02-01 …  6.6667
          3    2014-02-04 …         6  1  2014-03-01 …  6.6667
          3    2014-02-04 …         6  2  2014-04-01 …  6.6667
          3    2014-02-04 …         6  3  2014-05-01 …  6.6667
          3    2014-02-04 …         6  4  2014-06-01 …  6.6667
          3    2014-02-04 …         6  5  2014-07-01 …  6.6667

I inserted blank lines to separate the purchase_id groups so you can see how n increases from 0 to duration - 1 with each item in the group. As you can see, year_mon is equal to n months after the first day of the date_purchased month plus n months, and amount is equal to amount_income / duration.

We're almost done, but as you can see year_mon has repetition: 2014-01-01 is shown twice. This is great news, because we can then use GROUP BY to group by that column and SUM(amount) to get the total for that month:

SELECT
  DATE_ADD(
    DATE_ADD(
      MAKEDATE( YEAR(purchases.date_purchased), 1 ),
      INTERVAL MONTH(purchases.date_purchased) - 1 MONTH
    ),
    INTERVAL generator_16.n MONTH
  ) AS year_mon,
  SUM(purchases.amount_income / purchases.duration) AS total
FROM purchases
JOIN generator_16
ON generator_16.n BETWEEN 0 AND purchases.duration - 1
GROUP BY year_mon
ORDER BY year_mon;

The only difference between this query and the previous month is that we do GROUP BY year_mon and then SUM(amount_income / duration) to get total for the month, yielding this result (SQL Fiddle):

    year_mon   total
------------  ------
2013-12-01 …     7.5
2014-01-01 …    17.5
2014-02-01 …  6.6667
2014-03-01 …  6.6667
2014-04-01 …  6.6667
2014-05-01 …  6.6667
2014-06-01 …  6.6667
2014-07-01 …  6.6667

...and of course you can use DATE_FORMAT and CAST or ROUND to get nicely-formatted dates and amounts, or you can do that in your front-end code.