MySQL How do I show month over month gains or losses?

Question

MY question deals with month to month data changes..I want a simple (if possible) SQL query that will show the person , and month change grouped by person/month or similar.. the important part is to show gains and losses.

For Example I have a table: (date simplified )

id | Date      | Person | Sales
---|-----------|--------| -----
1  | 2010-9-01 | Alice  |  5
2  | 2010-8-01 | Alice  |  2

3  | 2010-9-01 | Peter  |  2
4  | 2010-8-01 | Peter  |  3

5  | 2010-9-01 | Bob    |  5
6  | 2010-8-01 | Bob    |  7

From this table I'm looking to get a report like this.

Month |  Person | Sales_total | Gain-Loss_from_Previous_month |
------  -------- ------------- -------------------------------
2010-9   Alice       7             +3
2010-9   Peter       5             -1
2010-9   Bob         12            -2

Can this be done as a pure MySQL query? O

Answer 1

You may want to try something like the following:

SELECT     CONCAT(YEAR(s1.date), '-', MONTH(s1.date)) month, 
           s1.person, 
           s2.total_sales sales_total,
           s1.sales - s3.sales gain_loss
FROM       sales s1
JOIN       (
              SELECT person, SUM(sales) total_sales FROM sales GROUP BY person
           ) s2 ON (s2.person = s1.person)
LEFT JOIN  sales s3 ON 
           (s3.date = DATE_SUB(s1.date, INTERVAL 1 MONTH) AND s3.person = s1.person)
WHERE      s1.date = STR_TO_DATE(CONCAT('01-', MONTH(NOW()), '-', YEAR(NOW())), '%d-%m-%Y')
GROUP BY   s1.person, s1.date
ORDER BY   s1.person, s1.date;

The WHERE clause part where s1.date = STR_TO_DATE(...) is basically restricting the s1.date to the current month and year. I have a feeling that there's a neater solution, but at the moment I can't think of a suitable alternative.

Test case:

CREATE TABLE sales (id int, date date, person varchar(40), sales int);

INSERT INTO SALES VALUES (1, '2010-9-01', 'Alice', 5);
INSERT INTO SALES VALUES (2, '2010-8-01', 'Alice', 2);
INSERT INTO SALES VALUES (3, '2010-9-01', 'Peter', 2);
INSERT INTO SALES VALUES (4, '2010-8-01', 'Peter', 3);
INSERT INTO SALES VALUES (5, '2010-9-01', 'Bob', 5);
INSERT INTO SALES VALUES (6, '2010-8-01', 'Bob', 7);

Result:

+--------+--------+-------------+-----------+
| month  | person | sales_total | gain_loss |
+--------+--------+-------------+-----------+
| 2010-9 | Alice  |           7 |         3 |
| 2010-9 | Bob    |          12 |        -2 |
| 2010-9 | Peter  |           5 |        -1 |
+--------+--------+-------------+-----------+
3 rows in set (0.00 sec)

Answer 2

If you only want to compare two months, here is a relatively simple query:

SELECT   '2010-9' month, 
         s1.person, 
         sum(s1.sales) sales_total,
         sum(s1.sales * (case when MONTH(s1.date) = 8 then -1 else 1 end)) gain_loss
FROM     sales s1
WHERE    s1.date >= STR_TO_DATE('01-08-2010', '%d-%m-%Y') AND
         s1.date < STR_TO_DATE('01-10-2010', '%d-%m-%Y')
GROUP BY s1.person