CODEWITHSUNDEEP
sqlms-access
Jossy
Jossy

Reputation: 999

How do I add two sum queries together?

I have the following two pseudo queries:

SELECT Sum(a)
FROM b
WHERE c

and

SELECT Sum(d)
FROM b
WHERE e

I want to sum these queries together to one value but I can't figure out the syntax. Note the FROM statement is the same ("b"). I've tried a UNION query but this gives me two values...

Upvotes: 1

Views: 139

Answers (5)

Lee Mac
Lee Mac

Reputation: 16015

Since both queries will always return a single record, you could alternatively cross join the two subqueries and simply add the results, e.g.:

select r1 + r2 from
(select sum(a) as r1 from b where c) t1,
(select sum(d) as r2 from b where e) t2

Upvotes: 2

mlamsarf
mlamsarf

Reputation: 26

Try to use this : 

;WITH 
t1 as ( select sum(a) as a from b where c>20)
,
t2 as (select sum(d) as d from b where e is not null)
select t1.b1+t2.c2 as s from t1 inner join t2 on t1.b1 != t2.c2

Upvotes: 1

Koushik Mitra
Koushik Mitra

Reputation: 33

Please try the following

Select sum(sumVal)

FROM

(SELECT Sum(a) sumVal FROM b where c

UNION

SELECT Sum(d) sumVal FROM e where f )

Upvotes: 1

forpas
forpas

Reputation: 164099

You can use iif() inside sum() where you apply the conditions:

select sum(iif(c, a, 0)) + sum(iif(e, d, 0))
from b

Upvotes: 2

HereGoes
HereGoes

Reputation: 1320

Try

SELECT SUM(col1) 
FROM
(
SELECT Sum(a) col1
FROM b
WHERE c

UNION

SELECT Sum(d) col1
FROM b
WHERE e)  t

Upvotes: 1

Related Questions