Subtracting values ​​in a MySQL table

Question

I have prices in two different tables and want to subtract them (current price for the last day) and ORDER them in DESC form. I was wondering if this could be done with 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 

Answer 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 question other than how I used LEFT JOIN. I believe if yesterday bets are not available for writing, the last three columns will contain NULL. Exit:

 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 

Answer 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.

He selects data from 2 tables: all data from table 1 and the corresponding data from table2.
If he cannot find the corresponding data from table2, he will replace the null values ​​instead of the missing rows. ( left join )

It then groups the ( group by ) rows based on table1.item name .
This concatenates several lines per element.
The having corrects this only by selecting the newest date strings from table2.

A small amendment is built into the select and having clauses to deal with the case where there is no data in table 2 to match table1.

Your request 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;