SQL SUMS?

From: Ken Leach <KLeach_at_ids.net>
Date: 1998/01/28
Message-ID: <34CF90A1.1B8F4D4F@ids.net>#1/1

Can someone help out with this query?

I have two tables
T1

cprno     balance     district
1             100            MA


T2
transno   cprno     balance
100       1             75
101       1             25

As you can see the sum of T2 (should) equal that of the same cprno in T1

I need to have BOTH totals on a sql returned row

SELECT T1.DISTRICT

       SUM(T1.BALANCE)
       SUM(T2.BALANCE)

FROM T1,T2
WHERE T1.CPRNO=T2.CPRNO
GROUP BY T1.DISTRICT should to return

MA 100 100

but I get

MA 200 100

I HAVE to sum the t1.balance since there could be more than one cprno per district.

The problem exists because the t1.balance = 100 is counted twice beause the detail has two lines.

how can I properly get the sums?

Thanks.. Received on Wed Jan 28 1998 - 00:00:00 CST