Mysql – Difference between two recordsets

MySQLPHP

I'm looking to calculate the difference between two record sets in MySQL.

Say I have the following table:

colname type attrbutes
id      uInt PK
date    date PK
count   uInt

With this data:

id   date      count
1    2000-1-1  15
1    2000-2-1  20
2    2000-1-1  20
2    2000-2-1  30

I'm trying to get the following result (if the user selects dates 2000-1-1 and 2000-2-1):

id  count
1   5
2   10

What is a good way to do this?

Best Answer

Using the same table structure and data

use test
DROP TABLE IF EXISTS test_dba;
CREATE TABLE `test_dba` (
  `id` int(11) DEFAULT NULL,
  `date_en` date DEFAULT NULL,
  `count_rec` int(11) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO test_dba VALUES
(1,'2000-01-01',15),(1,'2000-02-01',20),
(2,'2000-01-01',20),(2,'2000-02-01',30),
(3,'2000-01-01',20),(3,'2000-02-01',17);
SELECT * FROM test_dba;

Here is that data loaded

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test_dba;
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE `test_dba` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `date_en` date DEFAULT NULL,
    ->   `count_rec` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT INTO test_dba VALUES
    -> (1,'2000-01-01',15),(1,'2000-02-01',20),
    -> (2,'2000-01-01',20),(2,'2000-02-01',30),
    -> (3,'2000-01-01',20),(3,'2000-02-01',17);
Query OK, 6 rows affected (0.20 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_dba;
+------+------------+-----------+
| id   | date_en    | count_rec |
+------+------------+-----------+
|    1 | 2000-01-01 |        15 |
|    1 | 2000-02-01 |        20 |
|    2 | 2000-01-01 |        20 |
|    2 | 2000-02-01 |        30 |
|    3 | 2000-01-01 |        20 |
|    3 | 2000-02-01 |        17 |
+------+------------+-----------+
6 rows in set (0.00 sec)

mysql>

Please note that I added id 3 to demonstrate negative differences

Here is my proposed query

SELECT
    id,ABS(diff) diff,
    IF(diff>0,'Increase',IF(diff<0,'Decrease','NoDifference')) delta
FROM
(
    SELECT A.id,B.count_rec firstcount,C.count_rec lastcount,
    C.count_rec - B.count_rec diff
    FROM
    (
        SELECT id,MIN(date_en) mindate,MAX(date_en) maxdate FROM test_dba
        WHERE date_en BETWEEN '2000-01-01' AND '2000-02-01' GROUP BY id
    ) A
    INNER JOIN test_dba B ON A.id=B.id AND A.mindate=B.date_en
    INNER JOIN test_dba C ON A.id=C.id AND A.maxdate=C.date_en
) AA;

Notice I used the following:

  • MAX() / MIN() on the dates rather than on the counts
  • ABS() to get absolute value of difference

Here is the output

mysql> SELECT
    ->     id,ABS(diff) diff,
    ->     IF(diff>0,'Increase',IF(diff<0,'Decrease','NoDifference')) delta
    -> FROM
    -> (
    ->     SELECT A.id,B.count_rec firstcount,C.count_rec lastcount,
    ->     C.count_rec - B.count_rec diff
    ->     FROM
    ->     (
    ->         SELECT id,MIN(date_en) mindate,MAX(date_en) maxdate FROM test_dba
    ->         WHERE date_en BETWEEN '2000-01-01' AND '2000-02-01' GROUP BY id
    ->     ) A
    ->     INNER JOIN test_dba B ON A.id=B.id AND A.mindate=B.date_en
    ->     INNER JOIN test_dba C ON A.id=C.id AND A.maxdate=C.date_en
    -> ) AA;
+------+------+----------+
| id   | diff | delta    |
+------+------+----------+
|    1 |    5 | Increase |
|    2 |   10 | Increase |
|    3 |    3 | Decrease |
+------+------+----------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Related Question