Data gets populated from a particular table's date instead of all table in sqlite

android, android-sqlite, sqlite

Solution

Try the below.

What I have done is created a virtual table, containing unique months. To which there is info in one of the 3 tables for that month. Then the sums are bound to that month.

I have also removed the `ORDER BY` in the `SUM` clauses, as they don't change the result. Your `IFNULL` could also be changed, depending on how you expect it to work.

This method saves you writing out 3 lots of SUMs for each 3 fields (9 SUMs). If there are any typos, please fix accordingly.

NOTE that `month` is a keyword, in some SQL. So ideally, a better column name should be used.

SELECT month, 
(SELECT IFNULL (SUM(FTL.tradebalanceamount),0 ) 
   FROM farmertradelabel as FTL 
   WHERE FTL.mobileno = '9486032141'  AND strftime('%m-%Y', FTL.tradedate) = month) AS tradeamount,
(SELECT IFNULL (SUM(advanceamount),0)  
   FROM advancelabel 
   WHERE advancelabel.mobileno = '9486032141' AND strftime('%m-%Y', advancelabel.advancedate) = month) AS advanceamount,
(SELECT IFNULL (SUM(loantotalamount),0) 
  FROM loanlabel 
  WHERE loanlabel.mobileno = '9486032141' AND strftime('%m-%Y', loanlabel.loandate) = month) AS loanamount   

FROM
 (SELECT strftime('%m-%Y', tradedate) as 'month' FROM farmertradelabel
 UNION 
   SELECT  strftime('%m-%Y', advancedate) as 'month' FROM advancelabel
 UNION 
   SELECT strftime('%m-%Y', loandate) as 'month' FROM loanlabel
 ) as dateTable

Problem

Original source