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

Question

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

are you really looking for a result set, or do you just want the insert to happen in the table? How many rows are in the table? Do you have an auto_inc id that follows along? — Drew, Sep 14 '15 at 02:25
the original table has thousands of rows and hundreds of animal; but I want a null row field at the end of each animal! — cronos, Sep 14 '15 at 02:29
Why would the actual date be null? You do not have any rows in your table with a null date? Perhaps you mean that the past date would be null. In other words, there would be no previous date. — DuncanKinnear, Sep 14 '15 at 02:30
I think he wants a NULL so he can do what he wants to do, when this is done (which is suspect in my book... But ... that is off topic) — Drew, Sep 14 '15 at 02:31
I just need the resulting table to have exactly the same number of rows than the original data table; I don't care about the "actual_date" of the "past_date" columns. — cronos, Sep 14 '15 at 02:40
how can the 2 tables you showed, the before and after, satisfy `I just need the resulting table to have exactly the same number of rows than the original data table`, when what you showed doesn't do that — Drew, Sep 14 '15 at 02:41
although analytics functions will be added in mysql 8.0, this might be helpful. https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql — รยקคгรђשค, Mar 23 '18 at 09:43

DuncanKinnear · Answer 1 · 2015-09-14T04:05:13.733

1

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

edited Sep 14 '15 at 04:05

answered Sep 14 '15 at 02:38

DuncanKinnear

4,563
2
34
65

If if add a "left" before the second "join" I will get 3 nulls rows at the end of the resulting tables; why they did't obey the "GROUP BY c.animal"??? – cronos Sep 14 '15 at 03:00
Almost but the dates are in reverse order; i need them ascending going down! – cronos Sep 14 '15 at 03:53
1

@cronos, so change the `desc` to `asc` on the date. See my edit. – DuncanKinnear Sep 14 '15 at 04:04
@DucanKinnear now the NULL cells are located on top of the animal group; they must be at the end – cronos Sep 14 '15 at 12:31

Drew · Accepted Answer · 2015-09-14T03:31:13.857

1

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

edited Sep 14 '15 at 03:31

answered Sep 14 '15 at 02:52

Drew

24,851
10
43
78

No, the OP is not wanting to insert records into the inventory table with null dates. He wants the query to include records that do not have a previous date. – DuncanKinnear Sep 14 '15 at 03:03
Then explain this comment then @DuncanKinnear `the original table has thousands of rows and hundreds of animal; but I want a null row field at the end of each animal! – cronos 40 mins ago` ... I spent that time getting that out of him – Drew Sep 14 '15 at 03:09
Thanks @DuncanKinnear that's correct; @Drew; you need to subtract the the quantities of the same animals based on the date! – cronos Sep 14 '15 at 03:13
English is obviously his second language, and what I believe he _meant_ was that he wants null rows in the result set. If you look at what the query is actually trying to do, it is trying to find the difference in quantity between a record and a chronologically previous record. Obviously the first record for an animal will not have a previous record, therefore the resulting difference will be null. – DuncanKinnear Sep 14 '15 at 03:14
There, my name is memorialized as a column alias – Drew Sep 14 '15 at 03:19
You still haven't given him the differences in quantity. Look at his original query. – DuncanKinnear Sep 14 '15 at 03:21
We are all over the place here in version control @DuncanKinnear – Drew Sep 14 '15 at 03:21
well then look a little closer at the date column – Drew Sep 14 '15 at 03:22
English is my first language, but I am enrolled in ESL classes tho – Drew Sep 14 '15 at 03:23
Ok @Drew do you understand the word subtract? You are on the right track; all you need to do is to show the difference of the adjacent rows for the quantity column based on the same animal! – cronos Sep 14 '15 at 03:28
well I do understand that sometimes I want to subtract myself from the answer pool ! – Drew Sep 14 '15 at 03:32
...One more thing! How can I get the result of just one animal; for example: "dog" (maintaining a NULL cell at the end of course)? – cronos Sep 14 '15 at 03:37
1

just insert a new line near the end, putting `where x.animal='cat'` , for instance , on the line above the last one – Drew Sep 14 '15 at 03:53

Amit.S · Answer 3 · 2015-09-14T13:05:24.317

1

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

edited Sep 14 '15 at 13:05

answered Sep 14 '15 at 02:57

Amit.S

431
2
9

So where are the 2 dates that the OP had in his original query. They are probably important to him. – DuncanKinnear Sep 14 '15 at 03:05
@DuncanKinnear cronos mentioned in his/her last comment in the question that he don't care about the actual_date and past_date columns – Amit.S Sep 14 '15 at 03:08
Thanks @Amit.S almost there but not yet; the animals must be arranged by the animal column; and the NULL cell have to be at the end of the animals list. – cronos Sep 14 '15 at 03:12
No, your `ORDER BY` will not keep the results of every animal together. His query happens to show the difference decreasing, but that is just a red herring. The result he wants is grouped by animal. – DuncanKinnear Sep 14 '15 at 03:37
1

@cronos I have edited the order by clause that will keep all the animal together and quantity diff will be in DESC order – Amit.S Sep 14 '15 at 03:44
@Amit.S Now order is incorrect because the "rabbit" group must be 8, 16, 32, 64, 128, NULL – cronos Sep 14 '15 at 12:39
@cronos just change the order clause mate. sorry you want null on the bottom and rest in ASC order is it ? – Amit.S Sep 14 '15 at 13:04
@Amit.S yes; ascending by date, and grouped by animal with the NULL at the end of each group – cronos Sep 14 '15 at 16:17

score 1 · Answer 4 · answered Sep 14 '15 at 03:43

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.

Thanks @Emiliano but that give me the NULL cell at the beginning of each animal group; it must be at the end of each group. — cronos, Sep 14 '15 at 03:55

score 0 · Answer 5 · edited May 23 '17 at 12:07

0

"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

edited May 23 '17 at 12:07

Community

1
1

answered Sep 14 '15 at 02:45

Nitin Tripathi

1,224
7
17

You are correct; (I posted the same link at the beginning of this question)! – cronos Sep 14 '15 at 02:52

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

5 Answers5

The Query

The Results:

Linked