MYSQL has 2 tables. Same dates total. Compare and subtract dates

Question

​tablo1                             tablo2
--------------------------         ------------------------------
fiyat1         tarih1              fiyat2            tarih2
----------     ------------        -----------        -----------
1200           03-2017             2100               03-2017
1050           03-2017             5200               03-2017   
3250           04-2017             3200               04-2017
2501           04-2017              
6100           05-2017
1100           05-2017            

---

Collecting the same dates at price 1, collecting the same dates at price 2, subtract 2 totals, group by date.

I want to print something like this:

-----------------------
05-2017      7200
04-2017      2511
03-2017     -5050

The question is true, but the result is wrong. I tried this.

SELECT   tablo1.tarih1,
         tablo1.fiyat1,
         SUM(tablo1.fiyat1),
         tablo2.tarih2,
         tablo2.fiyat2,
         SUM(tablo1.fiyat1),
         (SUM(tablo1.fiyat1) - SUM(tablo2.fiyat2)) AS sonuc
FROM     tablo1 INNER JOIN
         tablo2 ON tablo1.tarih1 = tablo2.tarih2
GROUP BY tablo1.tarih1

Answer 1

With the table structure being as it is, the query that can be written to get the desired result is:

SELECT t1.tarih1, (COALESCE(t1.fiyat1, 0) - COALESCE(t2.fiyat2, 0)) AS sonuc
FROM
    (SELECT tarih1, SUM(fiyat1) AS fiyat1
         FROM tablo1
     GROUP BY tarih1
    ) AS t1
    LEFT JOIN
    (SELECT tarih2, SUM(fiyat2) AS fiyat2
         FROM tablo2
     GROUP BY tarih2
    ) AS t2
    ON t1.tarih1 = t2.tarih2
ORDER BY t1.tarih1 DESC;

However, I'd like to offer a couple of suggestions:

• It's generally a good idea to store the date in MySQL date format: YYYY-MM-DD. It'll be much easier for you to run yearly reports, if there ever was a need.
• As far as book-keeping is concerned, maybe you'll find the following Q&A to be of your interest: What is a common way to save 'debit' and 'credit' information?