Re: SQL SUMS?

From: Wolfgang Diestelkamp <wolfgang.nospam.diestelkamp_at_telia.se>
Date: 1998/01/29
Message-ID: <34D03A66.63C2@telia.se>#1/1

Ken Leach wrote:  

> 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.

From what you write it seems like t1 already contains the sum, so you do not want to (or HAVE to) sum up t1.balance. You want to select t1.balance as is, but since your are in a grouped query not grouped by t1.balance, you cannot select it (I think that's what you mean by HAVE to).
The trick you can use is:

SELECT T1.DISTRICT

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

FROM T1,T2
WHERE T1.CPRNO=T2.CPRNO
GROUP BY T1.DISTRICT where the MAX(T1.BALANCE) would give you the greatest of 100 and 100.

Hope this helps

/Wolfgang

Remove all letters except the 'o' from 'nospam' to email me Received on Thu Jan 29 1998 - 00:00:00 CST