Difference between two recordsets

Question

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?

Answer 1

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 !!!

Answer 2

Table structure that i used

mysql> show create table test_dba\G
*************************** 1. row ***************************
       Table: test_dba
Create Table: CREATE TABLE `test_dba` (
  `id` int(11) DEFAULT NULL,
  `date_en` date DEFAULT NULL,
  `count_rec` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

Data in table

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 |
+------+------------+-----------+
4 rows in set (0.00 sec)

If you will always have two records corresponding to each id then you can use query below.

If there is some other constraint please update that in your question

SELECT b.id , b.max_count-b.min_count FROM ( SELECT id,MAX(count_rec) as max_count ,MIN(count_rec) as min_count FROM test_dba WHERE date_en BETWEEN '2000-01-01' AND '2000-02-01' GROUP BY id ) b ;
+------+-------------------------+
| id   | b.max_count-b.min_count |
+------+-------------------------+
|    1 |                       5 |
|    2 |                      10 |
+------+-------------------------+
2 rows in set (0.00 sec)