Calculate sum of two sums

Question

Is there a way for me to calculate the sum of two sums? Take the following query (just an example query) how can I get a value for combined_total? when I run my query it says total1 is an Unknown column. Is there a way to get that value without having to run another sum combining the two sums? That just seems redundant and messy.

select sum(
    case when
        the_date = date_sub(curdate(), interval 1 year) 
    then amount else 0 end
) as total1, 
sum(
    case when 
        the_date between date_sub(curdate(), interval 1 year) 
        and date_add(date_sub(curdate(), interval 1 year), interval 1 day)
    then amount else 0 end
) as total2,
(total1 + total2) as combined_total

You'll need to use a subquery so you can reference the alias `total1` and `total2`. — Taryn, Dec 18 '14 at 21:07
That's your only option if you don't want to repeat the code. — Taryn, Dec 18 '14 at 21:11
I can run this `update tmp_days set total = (expiring + spent);` after my select. btw the select inserts into a tmp table. — Get Off My Lawn, Dec 18 '14 at 21:19

score 2 · Answer 1 · answered Dec 18 '14 at 23:10

Just like @bluefeet mentioned, subquery it, and fear not.

CREATE TABLE tbl
    (`type` varchar(1), `value` int, `active` int)
;

INSERT INTO tbl
    (`type`, `value`, `active`)
VALUES
    ('a', 1, 1),
    ('a', 1, 1),
    ('b', 1, 0),
    ('b', 1, 1),
    ('b', 1, 1),
    ('c', 2, 1),
    ('c', 2, 0)
;

select
  type,
  sum_active,
  sum_inactive,
  sum_active+sum_inactive as total
from (
  select 
    type,
    sum(if(active=1,value,0)) as sum_active,
    sum(if(active=0,value,0)) as sum_inactive
  from tbl
  group by type
) sums;

sqlfiddle: http://sqlfiddle.com/#!2/9699da/15/0

Calculate sum of two sums

1 Answers1