sql statement dont works like i want

Question

i need to get the sum from all "dblSoll" Values from tabBuchung and the sum from all "dblSoll" values from tabBuchungx in one row...

here are my definitions:

CREATE TABLE tabBuchung
(strKto char(50), 
 dblSoll int(50),
 dblHaben int(50));

CREATE TABLE tabKonten
(strKtoNr char(50),
strKtoBez char(50),
strKtoTyp char(50));

CREATE TABLE tabBuchungx
(strKto char(50),
dblSoll int(50),
dblHaben int(50));

insert into tabBuchung values ("1500", 50,0);
insert into tabBuchung values ("1600", 70,0);
insert into tabBuchung values ("1600", 130,0);
insert into tabBuchung values ("1700", 0, 800);
insert into tabBuchung values ("1800", 30, 22);
insert into tabBuchung values ("2000", 100, 0);
insert into tabBuchung values ("2000", 140, 0);

insert into tabBuchungx values ("1500", 0, 80);
insert into tabBuchungx values ("1600", 220, 0);
insert into tabBuchungx values ("1600", 80, 0);
insert into tabBuchungx values ("1700", 0, 44);
insert into tabBuchungx values ("1800", 10, 15);
insert into tabBuchungx values ("1900", 30, 0);
insert into tabBuchungx values ("1900", 10, 0);

here is my statement atm:

select tabBuchung.strKto, sum(tabBuchung.dblSoll) as newSaldo, sum(tabBuchungx.dblSoll) as oldSaldo
from tabBuchung
join tabBuchungx on tabBuchung.strKto = tabBuchungx.strKto
where tabBuchung.strKto = '1600'
group by tabBuchung.strKto, tabBuchung.dblSoll, tabBuchungx.dblSoll

thats what i get atm:

strkto newsaldo oldsaldo
1600     70         80
1600     70        220
1600    130         80
1600    130        220

but i want ONE row with:

strkto   newsaldo   oldsaldo
1600        200       300

can anyone help me pls?

Answer 1

Try this way:

select distinct
       t.strKto,
       (select sum(t1.dblSoll)
           from tabBuchung t1 
          where  t1.strKto = t.strKto) as oldsaldo,  
       (select sum(tx.dblSoll) 
         from tabBuchungx tx 
          where  tx.strKto = t.strKto) as newsaldo
from tabBuchung t    
where t.strKto = '1600'

Answer 2

You can't join the tables. You have to use a different approach. You have 2 matching strkto in both tables which will return all possible combinations of those rows. In your case 4 rows. This is how you could combine them:

select strKto, sum(newSaldo) as newSaldo, sum(oldSaldo) as oldSaldo 
from 
(
select strKto, dblSoll as newSaldo, 0 as oldSaldo
from tabBuchung
where strKto = '1600'
union all
select strKto, 0 as newSaldo, dblSoll as oldSaldo
from tabBuchungx
where strKto = '1600'
) a
group by strKto