lundi 23 mars 2015

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 ?


Aucun commentaire:

Enregistrer un commentaire