SQL: How to to SUM two values from different tables

Question

I have a number of tables with values I need to sum up. They are not linked either, but the order is the same across all the tables.

Basically, I would like to take this two tables:

CASH TABLE  
London  540
France  240
Belgium 340

CHEQUE TABLE
London  780
France  490
Belgium 230

To get an output like this to feed into a graphing application:

London  1320
France  730
Belgium 570

Hint `UNION`, `GROUP BY` and `SUM` will be useful. – zero323 Oct 17 '13 at 20:47 — zero323, Oct 17 '13 at 20:47

score 75 · Accepted Answer · answered Oct 17 '13 at 20:49

75

select region,sum(number) total
from
(
    select region,number
    from cash_table
    union all
    select region,number
    from cheque_table
) t
group by region

answered Oct 17 '13 at 20:49

Jonysuise

1,790
13
11

curious, why union all vs union? – fungusanthrax Aug 31 '21 at 13:56
2

@fungusanthrax `UNION` by itself will remove non-distinct (duplicate) rows. `UNION ALL` will keep all rows in the union so that the sum is accurate – Harrison Nov 09 '21 at 17:33

score 29 · Answer 2 · edited Jun 03 '20 at 19:27

29

SELECT (SELECT COALESCE(SUM(London), 0) FROM CASH) + (SELECT COALESCE(SUM(London), 0) FROM CHEQUE) as result

'And so on and so forth.

"The COALESCE function basically says "return the first parameter, unless it's null in which case return the second parameter" - It's quite handy in these scenarios." Source

edited Jun 03 '20 at 19:27

AlwaysANovice

983
4
15
34

answered Oct 17 '13 at 20:50

vector

359
1
6
12

1

'London' is an entry in the table not a column – Uchenna Ebilah Oct 17 '13 at 23:40
2

In this way, when even one of the SUMs is NULL, the entire "result" will be null, so that's the simplest way but the least trusted way, unfortunately... – TheCuBeMan Jan 12 '16 at 07:09
@TheCuBeMan, true, but that van be avoided by using COALESCE function – AlwaysANovice Jun 03 '20 at 04:24

vhadalgi · Answer 3 · 2014-06-27T02:47:11.640

3

you can also try this in sql-server !!

select a.city,a.total + b.total as mytotal from [dbo].[cash] a join [dbo].[cheque] b on a.city=b.city

demo

or try using sum,union

select sum(total)  as mytotal,city
from
(
    select * from cash union
    select * from cheque
) as vij
group by city

edited Jun 27 '14 at 02:47

answered Oct 18 '13 at 04:38

vhadalgi

7,027
6
38
67

score 0 · Answer 4 · answered Oct 17 '13 at 22:35

For your current structure, you could also try the following:

select cash.Country, cash.Value, cheque.Value, cash.Value + cheque.Value as [Total]
from Cash
join Cheque
on cash.Country = cheque.Country

I think I prefer a union between the two tables, and a group by on the country name as mentioned above.

But I would also recommend normalising your tables. Ideally you'd have a country table, with Id and Name, and a payments table with: CountryId (FK to countries), Total, Type (cash/cheque)

SQL: How to to SUM two values from different tables

4 Answers4

Linked