Hi Ken,
try:
SELECT A.DISTRICT,
A.SUMBALANCE,
B.SUMBALANCE
FROM
(SELECT T1.DISTRICT,
SUM(T1.BALANCE),
cprno
FROM
T1
GROUP BY
cprno, district)
A,
(SELECT
SUM(T2.BALANCE),
T2.cprno
FROM
T1
GROUP BY
cprno)
B
WHERE B.CPRNO=A.CPRNO
GROUP BY A.DISTRICT
Ken Leach wrote:
>
> 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..
--
Regards
Matthias Gresz :-)
Received on Thu Jan 29 1998 - 00:00:00 CST
