gistfile1.txt

SELECT 
		t1b.id as contract_id,
		t1a.unit_id,
		t1a.asset_id,
		t1b.date,
		t1b.due_date,
		(t1a.total +  + t1b.rental_open) as rental,
		SUM(t1b.advance_rental) as advance_rental,
		SUM(t1b.security_deposit) as security_deposit,
		SUM(t1b.surcharge) as surcharge,
		SUM(t1b.others) as others
FROM contract_details as t1a
INNER JOIN (
		SELECT
			a.id , b.date, b.due_date,
			IF(c.charges_type = 1 and c.unit_id is null,c.amount,0) as rental_open,
			SUM(IF(c.charges_type = 3,c.amount,0)) as advance_rental,
			SUM(IF(c.charges_type = 4,c.amount,0)) as security_deposit,
			SUM(IF(c.charges_type = 5,c.amount,0)) as surcharge,
			SUM(IF(c.charges_type = 6,c.amount,0)) as others,
			c.charges_type
		from contract_headers as a
		INNER JOIN property_charges_headers as b on a.id = b.contract_id
		INNER JOIN property_charges_details as c on c.header_id = b.id
		WHERE 
				a.status in("Approved","Renewed","Terminate") 
				and YEAR(b.date) = 2019
				and MONTH(b.date) = 4
				and b.status = "Approved"
				and c.charges_type in (1,3,4,5,6)
		GROUP BY a.id, c.charges_type
		ORDER BY a.id
) as t1b on t1a.header_id = t1b.id
GROUP BY  t1a.unit_id
ORDER BY t1b.id