I'm looking for a possibility to (re)use the results of a SQL select to get the values ​​of different sub-selects within the same query

First, I should get $ValueToUse by selecting like this:

SELECT `cb_campaignid` as ValueToUse FROM `#__team_groups` WHERE `id` = '$Value'

For the "ValueToUse" result, I would like to make the next selection in the same SQL query statement if possible.

SELECT coalesce (nullif (SUM(Amount), ''), '0') as general_amount, 
(SELECT SUM(c.cb_stand) FROM #__employees c, #__team_users u where c.user_id=u.user_id and u.group=(select id from #__team_groups where cb_campaignid='$ValueToUse')) as teamleden_tussenstand, 
(select title from #__customer_campaigns where id='$ValueToUse') as title, 
(select id from #__team_groups where cb_campaignid='$ValueToUse') as groupID, 
(select goal from #__customer_campaigns where id='$ValueToUse') as goal
 FROM #__customer_payments WHERE published = 1 and campaign_id = '$ValueToUse'

What is the best way to obtain these results?