how to add a field of 3 tables and display the results together

advertisements

im using sql server and I have 3 tables called accnt, acc1152, and acc1167. each of these tables has an amnt field and a accno field with a few thousand records in it. i need to sum the entire amnt column for acc1152 and acc1167, then sum the amnt column in accnt for every record with the accno = '1152' then sum then sum the amnt column in accnt for every record with the accno = '1167'. then display the 4 sums together.

so the final result should like this

    totals
  SUMacc1152
  SUMacc1167
  SUMaccnt A
  SUMaccnt B

Ive tried these with no success:

SELECT SUM(amnt) FROM acc1167,
SUM(amnt) FROM acc1152,
SUM(amnt) FROM accnt WHERE ACCNO = '1167',
SUM(amnt) FROM accnt WHERE ACCNO = '1152'

and

SELECT SUM(amnt) FROM acc1167
SELECT SUM(amnt) FROM acc1152
SELECT SUM(amnt) FROM accnt WHERE ACCNO = '1167'
SELECT SUM(amnt) FROM accnt WHERE ACCNO = '1152'


SELECT 'acc1167' as tbl_name,SUM(amnt) FROM acc1167
union
SELECT 'acc1152' as tbl_name,SUM(amnt) FROM acc1152
union
SELECT '1167' as tbl_name,SUM(amnt) FROM accnt WHERE ACCNO = '1167'
union
SELECT '1152' as tbl_name,SUM(amnt) FROM accnt WHERE ACCNO = '1152'

Related Articles