Difference between two recordsets 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?
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&gt; use test
Database changed
mysql&gt; DROP TABLE IF EXISTS test_dba;
Query OK, 0 rows affected (0.19 sec)

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

mysql&gt; INSERT INTO test_dba VALUES
    -&gt; (1,'2000-01-01',15),(1,'2000-02-01',20),
    -&gt; (2,'2000-01-01',20),(2,'2000-02-01',30),
    -&gt; (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&gt; 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&gt;


Please note that I added id 3 to demonstrate negative differences

Here is my proposed query

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

mysql&gt;


Give it a Try !!!