SELECT
a.id,
a.username,
a.balance,
(SELECT CONCAT(COUNT(*), ' kali') FROM transactions ts WHERE ts.user_id=a.id AND TYPE='Deposit' AND STATUS=1) AS 'Jumlah Deposit',
(SELECT COALESCE(SUM(ts.total),0) FROM transactions ts WHERE ts.user_id=a.id AND TYPE='Deposit' AND STATUS=1) AS 'Total Deposit',
(SELECT CONCAT(COUNT(*), ' kali') FROM transactions ts WHERE ts.user_id=a.id AND TYPE='Order' AND STATUS=1) AS 'Jumlah Order',
(SELECT COALESCE(SUM(ts.total),0) FROM transactions ts WHERE ts.user_id=a.id AND TYPE='Order' AND STATUS=1) AS 'Total Transaksi',
(SELECT COUNT(*) FROM transactions ts WHERE ts.downline_id = a.id) AS 'Jumlah Downline',
(SELECT COUNT(*) FROM transactions ts WHERE ts.downline_id = a.id)*50 AS 'Benefit Downline',
a.balance + (SELECT COALESCE(SUM(ts.total),0) FROM transactions ts WHERE ts.user_id=a.id AND TYPE='Order' AND STATUS=1) -
(SELECT COALESCE(SUM(ts.total),0) FROM transactions ts WHERE ts.user_id=a.id AND TYPE='Deposit' AND STATUS=1) -
((SELECT COUNT(*) FROM transactions ts WHERE ts.downline_id = a.id)*50) AS 'Selisih'

FROM accounts a
GROUP BY a.id