CODEWITHSUNDEEP
androidmysqlsqlitesum
user3547706
user3547706

Reputation:

Sum query not getting right output in sqlite

Hello friends i have 3 tables like below :

property_master -->p_id, p_name,r_id

transction_master -->t_id,p_id,amount,user_cat_id,r_id

user_trans_cat-->user_cat_id,cat_name,trans_id ,r_id

my query as below 

SELECT property_master.p_id AS "Product Code",
   property_master.p_name AS "Description",
   (SELECT SUM(amount)
    FROM transction_master ,property_master

   JOIN user_trans_cat ON transction_master.user_cat_id = user_trans_cat.user_cat_id
   WHERE user_trans_cat.trans_id =1 and transction_master.date between   '2015-01-01' and '2015-12-31'
   group by property_master.p_id
   ) AS "Income",
   (SELECT SUM(amount)
    FROM transction_master ,property_master

   JOIN user_trans_cat ON transction_master.user_cat_id = user_trans_cat.user_cat_id
    WHERE user_trans_cat.trans_id =2 and transction_master.date between   '2015-01-01' and '2015-12-31'
   group by property_master.p_id
   ) AS "Expense"
   FROM property_master   ,transction_master where  property_master.r_id = 1

   Group by property_master.p_id

Transaction table enter image description here

Output Came enter image description here i want total income , expense value as per proeprty wise but when i run above query it will gave me total income value for all property any idea how can i solve it ?

Upvotes: 1

Views: 92

Answers (2)

Zymon Castaneda
Zymon Castaneda

Reputation: 759

You can try this mate:

SELECT
    pm.p_id 'Product Code',
    pm.p_name 'Description',
    SUM(CASE WHEN utc.r_id = 1 THEN tm.amount ELSE 0 END) 'Income',
    SUM(CASE WHEN utc.r_id = 2 THEN tm.amount ELSE 0 END) 'Expense'
FROM
    transction_master tm
    JOIN property_master pm ON tm.p_id = pm.p_id
    JOIN user_trans_cat utc ON utc.trans_id = tm.t_id
WHERE
    tm.date BETWEEN '2015-12-31' AND '2015-01-01'
GROUP BY
    pm.p_id;

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

How about below - 

select
pm.p_id AS "Product Code",
pm.p_name AS "Description",
sum( case when utrc.trans_id =1 then tm.amount else 0 end ) as "Income",
sum( case when utrc.trans_id =2 then tm.amount else 0 end ) as "Expense"
from transction_master tm
join property_master pm on pm.p_id = tm.p_id
join user_trans_cat utrc on utrc.user_cat_id = tm.user_cat_id
where 
tm.date between   '2015-01-01' and '2015-12-31'
group by 
pm.p_id

UPDATE : If you need all the property to be listed even if they are not in transaction master then you need to change the query to select from property_master and then left join as

from property_master pm
left join transction_master tm on pm.p_id = tm.p_id
left join user_trans_cat utrc on utrc.user_cat_id = tm.user_cat_id

Now left join with where condition is nothing but inner join so the where condition needs to be moved as joining condition so the query becomes 

select
pm.p_id AS "Product Code",
pm.p_name AS "Description",
sum( case when utrc.trans_id =1 then tm.amount else 0 end ) as "Income",
sum( case when utrc.trans_id =2 then tm.amount else 0 end ) as "Expense"
from property_master pm
left join transction_master tm on pm.p_id = tm.p_id
and tm.date between   '2015-01-01' and '2015-12-31'
left join user_trans_cat utrc on utrc.user_cat_id = tm.user_cat_id 
group by 
pm.p_id

Upvotes: 1

Related Questions