0

I have the following table:

location eventdate value
loc1 2021-11-28 1
loc2 2021-11-28 2
loc1 2021-11-29 1
loc2 2021-11-29 3
loc1 2021-11-30 2
loc2 2021-11-30 5

And I'd like to compare the values from the oldest availavle day and the day before that to get this result:

location diff
loc1 1
loc2 2

What's the SQL statement to get this?

5
  • What is your MySQL version? Please tag your question accordingly. You may also want to consider following these suggestions.
    – mustaccio
    Nov 29, 2021 at 20:43
  • Hi, and welcome to dba.se! Please go to dbfiddle.uk and create your table(s) and input your data. This reduces duplication of effort, provides a single source of truth and saves on duplication of effort for those who might wish to answer. Help us to help you!
    – Vérace
    Nov 29, 2021 at 21:48
  • Is this only today and yesterday? Or any day and its previous day? What have you tried. Hint, use a self-join, join the table to itself with the second table aliased, and make the join criteria suit your question.
    – danblack
    Nov 30, 2021 at 0:16
  • Use 2 copies of your table.
    – Akina
    Nov 30, 2021 at 4:58
  • Here is a fiddle with a socultion joining the table to itself: dbfiddle.uk/… But I have to extend this because I don't know if there is data available for today when I run the select. So I have to compare the max. available the to the day before. Any hint for that?
    – jlai
    Nov 30, 2021 at 6:43

1 Answer 1

2

As others have pointed out in the comments, you can JOIN your table to itself, also known as a self join. You can achieve this like so:

SELECT 
    CurrentDay.location, 
    CurrentDay.value - PreviousDay.value AS diff
FROM YourTable CurrentDay
INNER JOIN YourTable PreviousDay
    ON CurrentDay.location = PreviousDay.location
    AND CurrentDay.eventdate = DATE_SUB(PreviousDay.eventdate, INTERVAL 1 DAY);

This self join takes your table and joins it to itself on the same location but where the eventdate differs by 1 day.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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