CODEWITHSUNDEEP
mysqlsqldatabasephpmyadmin
Ashley Staggs
Ashley Staggs

Reputation: 1567

SQL - Join 2 tables and also return difference in value between 2 rows

I am trying to return a list of products with various criteria (search terms, dates etc) and I have the following example SQL.

SELECT * 
FROM products  
JOIN ( SELECT date, price, product_id 
       FROM pricing 
       WHERE id IN ( (SELECT  MAX(id) 
                      FROM pricing 
                      GROUP BY product_id )  )  
        AND date = '2021-09-08' ) as current_price on products.id=price.product_id 
WHERE 1 
  AND sku like '%%'   
   OR title like '%%'  
ORDER BY id ASC

It pulls data from 2 tables: products & pricing.

Products

+----+----------------+--------+
| id |     title      |  sku   |
+----+----------------+--------+
|  1 | Product Name 1 | 654987 |
|  2 | Product Name 2 | 548879 |
|  3 | Product Name 3 | 541685 |
+----+----------------+--------+

Pricing

+----+------------+------------+-------+
| id | product_id |    date    | price |
+----+------------+------------+-------+
|  1 |          1 | 06-09-2021 | 1.99  |
|  2 |          2 | 06-09-2021 | 1.99  |
|  3 |          1 | 07-09-2021 | 3.99  |
|  4 |          3 | 06-09-2021 | 5.99  |
|  5 |          3 | 08-09-2021 | 6.99  |
|  6 |          1 | 08-09-2021 | 6.99  |
+----+------------+------------+-------+

Desired Output

+----+----------------+--------+---------------+-------------------+------------+
| id |     title      |  sku   | current_price | last_price_update | difference |
+----+----------------+--------+---------------+-------------------+------------+
|  1 | Product Name 1 | 654987 | 6.99          | 08-09-2021        | 2.5        |
|  2 | Product Name 2 | 548879 | 1.99          | 06-09-2021        | 0         |
|  3 | Product Name 3 | 541685 | 6.99          | 08-09-2021        | -1          |
+----+----------------+--------+---------------+-------------------+------------+

My issue is I want to have a difference column returned too which shows the difference between the latest price and the one before it.

I have tried a billion things but SQL is not my thing and i either return the full list of prices or nothing at all.

Using MariaDB version: 10.3.30

Thanks, Ashley

Upvotes: 1

Views: 103

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use row_number() to enumerate the prices and then conditional aggregation to get the two prices:

select p.*, pr.current_price, pr.previous_price,
       (pr.current_price - pr.previous_price) as diff
from products p join
     (select product_id,
             max(case when seqnum = 1 then price end) as current_price,
             max(case when seqnum = 2 then price end) as previous_price
      from (select pr.*,
                   row_number() over (partition by product_id order by date desc) as seqnum
            from pricing pr
           ) pr
      where seqnum <= 2
      group by product_id
      ) pr
     on pr.product_id = p.id;

Upvotes: 1

Related Questions