Subtracting values in MySQL Table

Question

I have prices in two different tables and want to subtract them (current price-last day price) and ORDER them in DESC form. I was wondering if it can be done using a single MySQL command.

Table Structure

Table 1
id | Item Name | Date       | Price
 1 | alpha     | 2011-10-05 | 10
 2 | beta      | 2011-10-05 | 12
 3 | gamma     | 2011-10-05 | 14 

Table 2
id | Item Name | Date       | Price
 1 | alpha     | 2011-10-04 | 8
 2 | beta      | 2011-10-04 | 10
 3 | gamma     | 2011-10-04 | 12
 4 | alpha     | 2011-10-03 | 4
 5 | beta      | 2011-10-03 | 6
 6 | gamma     | 2011-10-03 | 8

Solution 1

SELECT 
table1.id, table1.`Item Name`,
table1.`Date` AS CurrDate, table1.Price AS CurrPrice,
table2.`Date` AS PrevDate, table2.Price AS PrevPrice,
table1.Price - table2.Price AS Difference
FROM table1
LEFT JOIN table2 ON table1.id = table2.id AND table1.`Date` - INTERVAL 1 DAY = table2.`Date`
ORDER BY Difference DESC

There is nothing special about this query except the way I've used the LEFT JOIN. I believe if yesterday's rates for a record are not available, the the last three columns would contain NULL. Output:

id | Item Name | CurrDate   | CurrPrice | PrevDate   | PrevPrice | Difference
2  | beta      | 2011-10-05 | 12        | 2011-10-04 | 10        | 2
3  | gamma     | 2011-10-05 | 14        | 2011-10-04 | 12        | 2
1  | alpha     | 2011-10-05 | 10        | 2011-10-04 | 8         | 2

Solution 2

SELECT 
  a.price as price1
  , IFNULL(b.price,'(no data)') as price2
  , (a.price - IFNULL(b.price,0)) as difference
FROM table1 a
LEFT JOIN table2 b ON (a.`item name` = b.`item name`)
GROUP BY a.`item name`
HAVING IFNULL(b.`date`,'') = MAX(IFNULL(b.`date`,'')

Here's how it works.

It selects data from 2 tables: All data from table1 and matching data from table2.
If it cannot find matching data from table2 it will substitute null values in place of the missing rows. (left join)

Then it groups (group by) rows together based on table1.item name.
This combines multiple rows per item.
The having clause fixes this by only selecting the newest date rows from table2.

A small correction is build into the select and having clauses to deal with the case when there is no data in table2 to match table1.

Your query should be:

SELECT 
  s.closing as price1
  , IFNULL(sh.closing,'(no data)') as price2
  , (s.closing - IFNULL(sh.closing,0)) as difference 
FROM stocks s 
LEFT JOIN stockhistory sh ON (s.symbol = sh.symbol) 
GROUP BY s.symbol 
HAVING IFNULL(sh.edate,'') = MAX(IFNULL(sh.edate,'')
LIMIT 30 OFFSET 0;

Comments

[Saad Bashir]

Thanks for the code but I would really appreciate if you could explain it a little too so that it is helpful in the future :)

[Saad Bashir]

I am getting the following error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 2

[Johan]

@SaadBashir, My query does not have limit, please copy-paste the whole query in a comment and I'll have a look. (BTW limit 30 offset 0 is the last statement in the query, not part of the select clause)

[Saad Bashir]

This is the exact code i ran in the phpmyadmin SQL console area and got the above mentioned error: SELECT a.closing as price1, IFNULL(b.closing,'(no data)') as price2, (a.closing - IFNULL(b.closing,0)) as difference FROM stocks a LEFT JOIN stockhistory b ON (a.symbol = b.symbol) GROUP BY a.symbol HAVING IFNULL(b.edate,'') = MAX(IFNULL(b.edate,'')

[Johan]

@SaadBashir, there is no limit clause in that line! So it's clearly impossible for MySQL to complain 'bout syntax errors near limit.