SQL merge sum selects to one result

Question

I have 2 select queries against the same table which both contain only a sum() inside the select part, but differ in the where clause. What I need to do, is to sum the result of both the queries.

Example queries:

SELECT SUM(amount) 
FROM EXAMPLETABLE
WHERE  IDATE BETWEEN '2017/01/01' AND '2017/12/31' AND SOMESTATUS = 0

SELECT SUM(otherAmount) 
FROM EXAMPLETABLE
WHERE  IDATE BETWEEN '2018/01/01' AND '2018/12/31' AND SOMESTATUS = 1

What I need is something like

SELECT SUM(amount+otherAmount)
FROM EXAMPLETABLE

where amount and otherAmount are selected as in my example queries.

I tried to follow this question, but I couldn't get it to work. I also tried to replace the fields with a sub-select, but this doesn't work, because selects are not allowe inside an aggregate function

MS SQL, will add as tag – XtremeBaumer Jan 12 '18 at 07:06 — XtremeBaumer, Jan 12 '18 at 07:06

score 2 · Answer 1 · answered Jan 11 '18 at 15:30

2

This is an easy way you could do:

SELECT SUM(b.a)
FROM (
    SELECT SUM(amount) a
    FROM EXAMPLETABLE
    WHERE IDATE BETWEEN '2017/01/01'
            AND '2017/12/31'
        AND SOMESTATUS = 0

    UNION ALL

    SELECT SUM(otherAmount) a
    FROM EXAMPLETABLE
    WHERE IDATE BETWEEN '2018/01/01'
            AND '2018/12/31'
        AND SOMESTATUS = 1
    ) AS b

I just created a union between the two queries and sum the result.

answered Jan 11 '18 at 15:30

Rogério Carvalho Batista

462
2
7

could you please explain how this works? from my test i'd say it does work as i need it, but i sadly don't understand how – XtremeBaumer Jan 11 '18 at 15:42
Sure! Basically union all results of your both queries in one by using the command UNION ALL (note: you could use only UNION but it will hide equal results) and then I use these two queries as a subqueries by inserting it after a new FROM and between parenthesis and I called it as 'b' then, the main query just sum it. What is your error? What you happening? – Rogério Carvalho Batista Jan 11 '18 at 15:46
Do I understand it correctly, that with this query I would iterate through the table 2 times? – XtremeBaumer Jan 12 '18 at 07:10

score 1 · Accepted Answer · answered Jan 11 '18 at 15:31

You can do this conditional aggregation.

SELECT SUM(case when IDATE BETWEEN '2017/01/01' AND '2017/12/31' AND SOMESTATUS = 0 then amount else 0 end) +
SUM(case when IDATE BETWEEN '2018/01/01' AND '2018/12/31' AND SOMESTATUS = 1 then amount else 0 end) 
FROM EXAMPLETABLE

score 0 · Answer 3 · answered Jan 11 '18 at 16:04

0

Very simply :

SELECT SUM((1-SOMESTATUS)*amount + SOMESTATUS*(otherAmount))
FROM EXAMPLETABLE
WHERE IDATE BETWEEN '2018-01-01' AND '2018-12-31'

...be careful of your dates. I think you must replace slashes with dashes

answered Jan 11 '18 at 16:04

Nomikos Strigkos

113
2

the dates are fine, and your solution wont work for me, because the example queries are just examples and in reality are much more complex. – XtremeBaumer Jan 12 '18 at 07:09

score 0 · Answer 4 · answered Jan 12 '18 at 08:43

I think you can write this in SQL Server

SELECT
( SELECT SUM(amount) a FROM EXAMPLETABLE WHERE IDATE BETWEEN '2017/01/01' AND '2017/12/31' AND SOMESTATUS = 0 )
+
( SELECT SUM(otherAmount) a FROM EXAMPLETABLE WHERE IDATE BETWEEN 2018/01/01' AND '2018/12/31' AND SOMESTATUS = 1 )

SQL merge sum selects to one result

4 Answers4