How to select specific data from a table in MySQL and subtract it from the next date available in a subsequent row?

Question

This question evolved from How to create a query in MySQL to subtract consecutive rows based on the date and a distinctive field?; I have the following table:

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

Thanks to the users that contributed (in special https://stackoverflow.com/users/1816093/drew) now I am able to run the following query:

select
animal,
date,
quantity,
quantity_diff
from
(   SELECT
a.animal,
a.Date AS actual_date,
past_date.Date AS date,
a.quantity AS quantity,
(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 null,animal,null,null,null,2 as drewOrder from inventory
) x
where x.animal='rabbit' group by quantity_diff
order by x.animal,x.drewOrder,x.actual_date;

This is what I get:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| rabbit | 2015-01-01 |      508 |             8 |
| rabbit | 2015-01-02 |      524 |            16 |
| rabbit | 2015-01-03 |      556 |            32 |
| rabbit | 2015-01-04 |      620 |            64 |
| rabbit | 2015-01-05 |      748 |           128 |
+--------+------------+----------+---------------+

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

...I am pretty close but i just can't get it like I want it; which is like this:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| rabbit | 2015-01-01 |      500 |             8 |
| rabbit | 2015-01-02 |      508 |            16 |
| rabbit | 2015-01-03 |      524 |            32 |
| rabbit | 2015-01-04 |      656 |            64 |
| rabbit | 2015-01-05 |      620 |           128 |
| rabbit | 2015-01-06 |      748 |          null |
+--------+------------+----------+---------------+

I also should be able to change the "animal" and get its respective output:

cat:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| cat    | 2015-01-01 |      300 |             4 |
| cat    | 2015-01-02 |      304 |          null |
+--------+------------+----------+---------------+

dog:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| dog    | 2015-01-01 |      400 |             2 |
| dog    | 2015-01-02 |      402 |          null |
+--------+------------+----------+---------------+

Jorge Campos · Accepted Answer · 2015-10-08T05:06:43.397

2

Well there is a simpler way to do it without that big query with joins and unions. You need an Window Function for your specific problem it would be a LEAD function.

LEAD(`quantity`) over (Partition By `date` order by `date`)

But unfortunately MySql doesn't support those functions so, you have to mimic it with query variables.

The solution as you want would be:

select animal,
       `date`,
       quantity,
       lead-quantity quantity_diff
from ( select i.animal, 
              i.`date`,
              @qt as lead,
              @qt := i.quantity as quantity
        from inventory i
       where i.animal = 'rabbit' 
       order by `date` desc
     ) tab
order by `date`;

You can check it working here: http://sqlfiddle.com/#!9/c77d8/182

And for the cat: http://sqlfiddle.com/#!9/c77d8/183

And for the dog: http://sqlfiddle.com/#!9/c77d8/184

I was frustrated to know that MySql doesn't support the Window Functions until I find this article: Analytic functions: FIRST_VALUE, LAST_VALUE, LEAD, LAG which lead me to learn how to mimic it. I suggest you to favorite it as I did!

Hope it helps!

edited Oct 08 '15 at 05:06

answered Oct 08 '15 at 04:56

Jorge Campos

22,647
7
56
87

I am using version `5.6.20` and for some reason the first time I run the query (in the command line) I get all `NULL` in the `quantity_diff` column. Then if I execute the query again I get everything fine except the last row, which displays this: `| rabbit | 2015-01-06 | 748 | -248 |` If I run the query in `phpmyadmin` I also get all `NULL` in the `quantity_diff` column (every time I run it); the same thing happens in `php`. What am I doing wrong? – cronos Oct 08 '15 at 13:59
1

I don't know what could be wrong. This works here on my end at the 5.4.* version, also 5.3.* version and also on the sql fiddle which uses 5.6.21 version see it here: http://sqlfiddle.com/#!9/c77d8/188 To see what are you doing wrong I would need to see what you did if there is ANYTHING different from what I gave, maybe, if that is the case. Create an sqlfiddle with your try IF it is different – Jorge Campos Oct 08 '15 at 14:24
The only difference I see is that you are using the version 5.5.* on your fiddle, that's really odd because I've tested it here on the versions I said 5.4.x, 5.3.x and 5.6. So I tested it here with another clients the only one in the version 5.6 that presents your behavior was the MySql WorkBench. The very first run it returns all null and the second run it returns as your link on fiddle (with 5.5 version). So I start to digg it. What I found out is that in order to it run every time you need to set the @qt variable prior to the query execution, though I don't know Why! I will ask a question – Jorge Campos Oct 08 '15 at 16:07
on dba.stackexchange to see if this is some sort of configuration of the database. – Jorge Campos Oct 08 '15 at 16:08
1

It is really really odd, on the version 5.6 if you define the variable prior to the query execution like `set @qt:=null; select .....` it always work, but not on the 5.5 version... I wil create the question there and add the link here so we can see what it is happening. – Jorge Campos Oct 08 '15 at 16:14
1

Lets see what they have to say, here is the [Question](http://dba.stackexchange.com/questions/117449/really-strange-behavior-using-variables-in-mysql-query) – Jorge Campos Oct 08 '15 at 16:31
@JorgeCampos thnx for the update. I have seen another note of 5.3 (or was it 5.4?) in some SO thread but never actually encountered such a version in real life! (will delete teh comments in 10 mins, sorry OP for the invasion with unrealted stuff;) – ypercubeᵀᴹ Oct 08 '15 at 17:57
1

Well it will be nice to have it on just one single query but by adding `@qt:=null;` at the beginning it is possible to reach the my goal, thanks @Jorge – cronos Oct 09 '15 at 00:52

How to select specific data from a table in MySQL and subtract it from the next date available in a subsequent row?

1 Answers1

Linked