gistfile1.txt

select
	t1.id, t1.title, IF(t2.amount is not null,SUM(t2.amount),0)  as amount, t1.f1,t1.f2,t1.f3,t1.f4,
	CASE
		WHEN (t1.f1>0 and t1.f2 = 0 and t1.f3 = 0 and t1.f4 = 0) THEN
			(SELECT count(*) from gl_accounts as g where g.f1 = t1.f1 )
		WHEN (t1.f1>0 and t1.f2 > 0 and t1.f3 = 0 and t1.f4 = 0) THEN 
			(SELECT count(*) from gl_accounts as g where g.f1 = t1.f1 and g.f2 = t1.f2 )
		WHEN (t1.f1>0 and t1.f2 > 0 and t1.f3 > 0 and t1.f4 = 0) THEN 
			(SELECT count(*) from gl_accounts as g where g.f1 = t1.f1 and g.f2 = t1.f2 and g.f3 = t1.f3)
		WHEN (t1.f1>0 and t1.f2 > 0 and t1.f3 > 0 and t1.f4 > 0) THEN 
			(SELECT count(*) from gl_accounts as g where g.f1 = t1.f1 and g.f2 = t1.f2 and g.f3 = t1.f3 and g.f4 = t1.f4)
		ELSE
				0
	END as has_child
from gl_accounts as t1 
left join (
		select 
				a.id as title_id,
				a.f1,a.f2,a.f3,a.f4,
				b.amount as amount
		from gl_accounts as a
		LEFT JOIN posting_gls as b on b.gl_account_id = a.id
		LEFT JOIN postings as c on c.id = b.posting_id
		LEFT JOIN cv_post_details as d on d.id = IF(c.doc_type = "CV",c.attachment_no,1)
		where d.type = "Posting"
		and c.status in ("Finalized","Posted")
		and b.entry_type = "Internal"
		and c.post_date <= "2018-03-15 00:00:00"
) as t2 on t1.id = t2.title_id
GROUP BY t1.id