SQL:如何从不同的表中求和两个值

Okay.

好吧。

I've been put on a project at work, and though I've got some SQL skills, they are very rusty.

我已经在工作中加入了一个项目，虽然我有一些SQL技能，但它们都很生疏。

One of the scenarios at work has left me with 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

Please help.

请帮助。

4 个解决方案

#1

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

#2

SELECT (SELECT SUM(London) FROM CASH) + (SELECT SUM(London) FROM CHEQUE) as result

'And so on and so forth.

等等。

#3

you can also try this in sql-server !!

您也可以在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

#4

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)

但我也建议你把你的桌子恢复正常。理想情况下，您应该有一个带Id和名称的国家表，以及一个带CountryId (FK to countries)的付款表，总数，类型(现金/支票)

#1

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

#2

SELECT (SELECT SUM(London) FROM CASH) + (SELECT SUM(London) FROM CHEQUE) as result