Mysql – How to merge these 3 queries into 1

join;MySQL

I am new to sql (using mysql) and I have the following problem. I need to create a table where first column is some months, and the next three are :
first column: total amount of previous months (from the one stated in 1st column)
second: total amount of current month
third: total amount of the following months.

Now I have done it in three seperate queries, and what I want to do next, is combine them somehow. I am totally new to sql, so my initial idea was to create three tables and inner join them, but I guess this is not neat approach. Is there any way I can do that properly?

I am attaching my 3 queries, plus my output:

# 1 column
 select month(rdate),
(SELECT SUM(ramaount) 
 FROM my_table 
 WHERE (month(rdate) <(month(a.rdate)))
 and year(rdate)=2015
) as total_previous
FROM my_table a
where year(rdate)=2015
group by month(rdate)
order by year(rdate),month(rdate);


# 2 column
select
month(rdate) as month_2015,
sum(ramaount)
from     my_table
where    year(rdate) in (2015)
group by   month(rdate);


# 3h colum
SELECT month(rdate),
(SELECT SUM(ramaount) 
 FROM my_table 
 WHERE (month(rdate) >(month(a.rdate)))
 and year(rdate)=2015
) as total_next
FROM my_table a
where year(rdate)=2015
group by month(rdate);

My three results currently are:

1, 
3, 375
5, 1325
6, 3055
7, 4255
8, 10255
9, 10555
10, 10755
12, 10935



1, 375
3, 950
5, 1730
6, 1200
7, 6000
8, 300
9, 200
10, 180
12, 1200


1, 11760
3, 10810
5, 9080
6, 7880
7, 1880
8, 1580
9, 1380
10, 1200
12,

Best Answer

One alternative is to trade the correlated subqueries and, potentially, joins for using variables as well as a lot of nesting and sorting.

The starting point of the approach I am suggesting will be this query:

SELECT
  MONTH(rdate)  AS month_2015,
  SUM(ramaount) AS this_amount
FROM
  my_table
WHERE
  YEAR(rdate) = 2015
GROUP BY
  MONTH(rdate)

which is essentially your second query. According to your example, the output would be this:

month_2015  this_amount
----------  -----------
1           375
3           950
5           1730
6           1200
7           6000
8           300
9           200
10          180
12          1200

The following query uses that as a derived table to calculate the running total from the first to the last month with the help of a variable:

SELECT
  month_2015,
  this_amount,
  @past_amount := @past_amount + this_amount AS past_amount
FROM
  (SELECT @past_amount := 0) AS x,
  (
    SELECT
      MONTH(rdate)  AS month_2015,
      SUM(ramaount) AS this_amount
    FROM
      my_table
    WHERE
      YEAR(rdate) = 2015
    GROUP BY
      MONTH(rdate)
  ) AS sub
ORDER BY
  month_2015 ASC

In your case the returned rows would look like this:

month_2015  this_amount  past_amount
----------  -----------  -----------
1           375          375
3           950          1325
5           1730         3055
6           1200         4255
7           6000         10255
8           300          10555
9           200          10755
10          180          10935
12          1200         12135

Never mind that the past_amount value includes the current month whereas you seem to require otherwise – that will be rectified later.

The next step would be to use the above as a derived table again and use the same method to calculate future_amount while sorting the rows in the opposite order:

SELECT
  month_2015,
  this_amount,
  past_amount,
  @future_amount := @future_amount + this_amount AS future_amount
FROM
  (SELECT @future_amount := 0) AS x,
  (
    SELECT
      month_2015,
      this_amount,
      @past_amount := @past_amount + this_amount AS past_amount
    FROM
      (SELECT @past_amount := 0) AS x,
      (
        SELECT
          MONTH(rdate)  AS month_2015,
          SUM(ramaount) AS this_amount
        FROM
          my_table
        WHERE
          YEAR(rdate) = 2015
        GROUP BY
          MONTH(rdate)
      ) AS sub
    ORDER BY
      month_2015 ASC
  ) AS sub
ORDER BY
  month_2015 DESC

This would be the result:

month_2015  this_amount  past_amount  future_amount
----------  -----------  -----------  -------------
12          1200         12135        1200
10          180          10935        1380
9           200          10755        1580
8           300          10555        1880
7           6000         10255        7880
6           1200         4255         9080
5           1730         3055         10810
3           950          1325         11760
1           375          375          12135

Again, do not worry at this point about the future_amount results including the current month's amount.

Your next, and last, step will be to nest the previous query one level more in order to sort the results back in the ascending order of months as well as to adjust past_amount and future_amount as past_amount - this_amount and future_amount - this_amount, respectively:

SELECT
  month_2015,
  NULLIF(past_amount   - this_amount, 0) AS past_amount,
  this_amount,
  NULLIF(future_amount - this_amount, 0) AS future_amount
FROM
  (
    SELECT
      month_2015,
      this_amount,
      past_amount,
      @future_amount := @future_amount + this_amount AS future_amount
    FROM
      (SELECT @future_amount := 0) AS x,
      (
        SELECT
          month_2015,
          this_amount,
          @past_amount := @past_amount + this_amount AS past_amount
        FROM
          (SELECT @past_amount := 0) AS x,
          (
            SELECT
              MONTH(rdate)  AS month_2015,
              SUM(ramaount) AS this_amount
            FROM
              my_table
            WHERE
              YEAR(rdate) = 2015
            GROUP BY
              MONTH(rdate)
          ) AS sub
        ORDER BY
          month_2015 ASC
      ) AS sub
    ORDER BY
      month_2015 DESC
  ) AS sub
ORDER BY
  month_2015 ASC
;

And this is would you should get as the result:

month_2015  past_amount  this_amount  future_amount
----------  -----------  -----------  -------------
1           NULL         375          11760
3           375          950          10810
5           1325         1730         9080
6           3055         1200         7880
7           4255         6000         1880
8           10255        300          1580
9           10555        200          1380
10          10755        180          1200
12          10935        1200         NULL

I additionally used the NULLIF function on the subtractions to substitute NULLs for what otherwise would be 0s in the first row's past_amount and the last row's future_amount, because that was what your results seemed to suggest to me you wanted. If a 0 in both cases would do just fine, then do not use NULLIF.

A demo of this approach is available at Rextester and SQL Fiddle.

We could also simplify by calculating the total first, so we don't have to do running sums, only one forward:

SELECT
  month_2015,
  @past_amount := @past_amount + @this_amount AS past_amount,
  @this_amount := this_amount                 AS this_amount, 
  total_amount - @past_amount - @this_amount  AS future_amount
FROM
  (SELECT @past_amount := 0, @this_amount := 0) AS x,

  (
    SELECT
      SUM(ramaount) AS total_amount
    FROM
      my_table
    WHERE
      YEAR(rdate) = 2015
  ) AS t,

  (
    SELECT
      MONTH(rdate)  AS month_2015,
      SUM(ramaount) AS this_amount
    FROM
      my_table
    WHERE
      YEAR(rdate) = 2015
    GROUP BY
      MONTH(rdate)
  ) AS sub 
;

An extra logical table scan in the form of the total amount across the year has been added in exchange for extra sorts of the result set.

Both solutions at Rextester.

Related Question