Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer

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)
share|improve this answer
    
i don't want to use MIN()/MAX() the result can be negative(ex: jan=20, feb=10 ; feb - jan = -10) –  borrel Jul 9 '13 at 10:05
    
In that case what you want in the result ? and will there be always two records corressponding to each id ? –  Abdul Manaf Jul 9 '13 at 10:10
    
no but the user can only select 2 dates(so then yes) –  borrel Jul 9 '13 at 15:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.