SELECT
sum(
if(bids.type IN (0) AND bids.status IN ('success'), bids.sum, 0)
) as `income`,
sum(
if(bids.type IN (1,2) AND bids.status IN ('realpay', 'success', 'verify'), bids.sum, 0)
) as `outcome`,
sum(
CASE
WHEN bids.type IN (0) AND bids.status IN ('success')
THEN bids.sum
WHEN bids.type IN (1, 2) AND bids.status IN ('realpay', 'success', 'verify')
THEN -bids.sum
ELSE 0
END
) as `balance`
FROM ...
Один из вариантов: https://stackoverflow.com/a/63478387
Но как замечали ранее,конечно, лучше избавиться от json