CODEWITHSUNDEEP
mysql
cronos
cronos

Reputation: 568

How to create a query in MySQL to subtract consecutive rows based on the date and a distinctive field?

Based on SQL subtract two rows based on date and another column I had a good idea but I need something else.

I have the following table (inventory):

animal   date         quantity

dog      2015-01-01   400
cat      2015-01-01   300
dog      2015-01-02   402
rabbit   2015-01-01   500
cat      2015-01-02   304
rabbit   2015-01-02   508
rabbit   2015-01-03   524
rabbit   2015-01-04   556
rabbit   2015-01-05   620
rabbit   2015-01-06   748

By running this query:

SELECT a.animal, a.Date
AS actual_date, past_date.Date
AS past_date, (a.quantity - past_date.quantity)
AS quantity_diff
FROM inventory a
JOIN
(SELECT b.animal, b.date AS date1,
(SELECT MAX(c.date)
FROM inventory c
WHERE c.date < b.date AND c.animal = b.animal
GROUP BY c.animal)
AS date2 
FROM inventory b)
AS original_date ON original_date.animal = a.animal
AND original_date.date1 = a.date
JOIN
inventory past_date
ON past_date.animal = a.animal
AND past_date.date = original_date.date2

I get this:

animal   actual_date   past_date   quantity_diff

dog      2015-01-02    2015-01-01  2
cat      2015-01-02    2015-01-01  4
rabbit   2015-01-02    2015-01-01  8
rabbit   2015-01-03    2015-01-02  16
rabbit   2015-01-04    2015-01-03  32
rabbit   2015-01-05    2015-01-04  64
rabbit   2015-01-06    2015-01-05  128

What I want to get is this (see quantity_diff column):

animal   quantity_diff

cat      4
cat      NULL
dog      2
dog      NULL
rabbit   8
rabbit   16
rabbit   32
rabbit   64
rabbit   128
rabbit   NULL

http://sqlfiddle.com/#!9/c77d8/11

Upvotes: 5

Views: 1711

Answers (5)

Amit.S
Amit.S

Reputation: 441

I have changed your query a bit which will get you the desired result and used LEFT JOIN on the tables:

SELECT a.animal,(a.quantity - past_date.quantity) AS quantity_diff
FROM inventory a
LEFT JOIN
(SELECT b.animal, b.date AS date1,
    (SELECT MAX(c.date)
    FROM inventory c
    WHERE c.date < b.date AND c.animal = b.animal
    GROUP BY c.animal
    )
AS date2 
FROM inventory b
)
AS original_date ON original_date.animal = a.animal
AND original_date.date1 = a.date
LEFT JOIN inventory past_date
ON past_date.animal = a.animal
AND past_date.date = original_date.date2 
ORDER BY animal ,past_date.quantity ASC

RESULT IS :

cat (null)
cat 4
dog (null)
dog 2
rabbit  (null)
rabbit  8
rabbit  16
rabbit  32
rabbit  64
rabbit  128

Upvotes: 1

DuncanKinnear
DuncanKinnear

Reputation: 4643

To make the query do what you want you need to put LEFT before the JOIN keywords, and add an ORDER BY clause.

So the resulting query is:

SELECT a.animal, a.Date
AS actual_date, past_date.Date
AS past_date, (a.quantity - past_date.quantity)
AS quantity_diff
FROM inventory a
LEFT JOIN
(SELECT b.animal, b.date AS date1,
(SELECT MAX(c.date)
FROM inventory c
WHERE c.date < b.date AND c.animal = b.animal
GROUP BY c.animal)
AS date2 
FROM inventory b)
AS original_date ON original_date.animal = a.animal
AND original_date.date1 = a.date
LEFT JOIN
inventory past_date
ON past_date.animal = a.animal
AND past_date.date = original_date.date2
ORDER BY a.animal asc, a.date asc

Upvotes: 1

Emiliano Sangoi
Emiliano Sangoi

Reputation: 921

Try this query:

SELECT L.animal,L.date,
(SELECT date FROM inventory WHERE animal=L.animal AND date<L.date ORDER BY date DESC LIMIT 1) AS 'past_date',
(L.quantity-(SELECT quantity FROM inventory WHERE animal=L.animal AND date<L.date ORDER BY date DESC LIMIT 1)) AS 'quantity_diff'
FROM inventory AS L
GROUP BY (CONCAT(L.animal,'-',L.date));

This query is going to search for each animal+date what is the most recent date before the current date. For example, for the first record:

dog      2015-01-01   400

The most recent date before '2015-01-01' is null, so there isn't anything to substract from the current quantity (400). For the second record:

dog      2015-01-02   402

The most recent date for current animal(dog), which it's before the current date('2015-01-02') is '2015-01-01', which has a quantity of 402, so the difference is 402-400=2. The process is the same for each animal.

Upvotes: 1

Drew
Drew

Reputation: 24960

The Question has changed a little bit, here is my current understanding of what you want.

CREATE TABLE inventory
    (`animal` varchar(6), `date` date, `quantity` int);
INSERT INTO inventory
    (`animal`, `date`, `quantity`)
VALUES
    ('dog', '2015-01-01', 400),
    ('cat', '2015-01-01', 300),
    ('dog', '2015-01-02', 402),
    ('rabbit', '2015-01-01', 500),
    ('cat', '2015-01-02', 304),
    ('rabbit', '2015-01-02', 508),
    ('rabbit', '2015-01-03', 524),
    ('rabbit', '2015-01-04', 556),
    ('rabbit', '2015-01-05', 620),
    ('rabbit', '2015-01-06', 748);

The Query

select animal,actual_date,past_date,quantity_diff
from
(   SELECT a.animal, a.Date
    AS actual_date, past_date.Date
    AS past_date, (a.quantity - past_date.quantity)
    AS quantity_diff,
    1 as drewOrder
    FROM inventory a
    JOIN
    (SELECT b.animal, b.date AS date1,
    (SELECT MAX(c.date)
    FROM inventory c
    WHERE c.date < b.date AND c.animal = b.animal
    GROUP BY c.animal)
    AS date2 
    FROM inventory b)
    AS original_date ON original_date.animal = a.animal
    AND original_date.date1 = a.date
    JOIN
    inventory past_date
    ON past_date.animal = a.animal
    AND past_date.date = original_date.date2
    union
    select distinct animal,null,null,null,2 as drewOrder from inventory
) x
order by x.animal,x.drewOrder,x.actual_date;

The Results:

+--------+-------------+------------+---------------+
| animal | actual_date | past_date  | quantity_diff |
+--------+-------------+------------+---------------+
| cat    | 2015-01-02  | 2015-01-01 |             4 |
| cat    | NULL        | NULL       |          NULL |
| dog    | 2015-01-02  | 2015-01-01 |             2 |
| dog    | NULL        | NULL       |          NULL |
| rabbit | 2015-01-02  | 2015-01-01 |             8 |
| rabbit | 2015-01-03  | 2015-01-02 |            16 |
| rabbit | 2015-01-04  | 2015-01-03 |            32 |
| rabbit | 2015-01-05  | 2015-01-04 |            64 |
| rabbit | 2015-01-06  | 2015-01-05 |           128 |
| rabbit | NULL        | NULL       |          NULL |
+--------+-------------+------------+---------------+

Upvotes: 1

Nitin Tripathi
Nitin Tripathi

Reputation: 1254

"Left outer Join" should solve your problem.

Something similar is already present in below question, please check Mike Parkhill's answer

SQL subtract two rows based on date and another column

Upvotes: 0

Related Questions