1

I have three SQL selects, the results of which I need to add together. Two of the three use fairly complex joins. 

select sum(field_one) from t_a join t_b on (t_a.bid = t_b.id) where t_b.user_id=:id
select sum(field_two) from t_c join t_d on (t_c.did = t_d.id) where t_d.user_id=:id
select sum(field_three) from t_e where t_e.user_id=:id

What I need is the sum of all three values. sum(field_one)+sum(field_two)+sum(field_three). Is there anyway to do this in a single statement?

Daniel Bingham
  • 12,414
  • 18
  • 67
  • 93
  • try using this select( (select 15) + (select 10) + (select 20)) logic check my answer below – Thakur Oct 05 '10 at 11:25

3 Answers3

8

You could UNION ALL them.
Do not use UNION, since it omits duplicate values (5+5+5 would result in 5).

Select Sum(s)
From
(
  Select Sum(field_one) As s ...
  Union All
  Select Sum(field_two) ...
  Union All
  Select Sum(field_three) ...
) x
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
5

You can do this without using Union like this

Sample Query

select( (select 15) + (select 10) + (select 20))

Your Query

select
(
    (select sum(field_one) from t_a join t_b on (t_a.bid = t_b.id) where t_b.user_id=:id) +
    (select sum(field_two) from t_c join t_d on (t_c.did = t_d.id) where t_d.user_id=:id) +
    (select sum(field_three) from t_e where t_e.user_id=:id) 
)
Thakur
  • 1,890
  • 5
  • 23
  • 33
3

You can use a UNION and a subselect to do that:

select sum(`sum`) FROM
(
  select sum(field_one) as `sum` from t_a join t_b on (t_a.bid = t_b.id) where t_b.user_id=:id
  UNION ALL
  select sum(field_two) from t_c join t_d on (t_c.did = t_d.id) where t_d.user_id=:id
  UNION ALL
  select sum(field_three) from t_e where t_e.user_id=:id
) as x;

Edit: Updated my answer to use UNION ALL, as suggested by Peter Lang.

Dennis Haarbrink
  • 3,738
  • 1
  • 27
  • 54