Sum records from two tables in one SELECT statement

Sum records from two tables in one SELECT statement

Post by Robbert te Riel » Thu, 20 Apr 2000 04:00:00



Hi,

I have the following problem. I want to summerize records from two table
which are related to each other by a field.

i.e

TableOne
Custnr        Money
100            40
100            50
150            10
150            40

TableTwo
Custnr        Quantity
100             100
100              200
150             50
150             60

As result from the SELECT statement I want the following

Custnr        Money         Quantity
100             90                300
150             50                 110

I tried the following
SELET Custnr, SUM(DISTINCT Money), SUM(DISTINCT Quantity) FROM TableOne,
TableTwo INNER JOIN TableOne.Custnr = TableTwo.Custnr.
This works, but I think it's not the proper way (performance etc) to do it
like this.

Is this the correct way of selecting the records, or is there a more
efficient way?

Thx.

Robbert

 
 
 

Sum records from two tables in one SELECT statement

Post by Robbert te Riel » Thu, 20 Apr 2000 04:00:00


Sorry,

I put down a wrong SQL statement,

This is the proper one

I tried the following
SELET Custnr, SUM(DISTINCT Money), SUM(DISTINCT Quantity) FROM TableOne
INNER JOIN TableTwo ON TableOne.Custnr = TableTwo.Custnr.
 This works, but I think it's not the proper way (performance etc) to do it
 like this.



Quote:> Hi,

> I have the following problem. I want to summerize records from two table
> which are related to each other by a field.

> i.e

> TableOne
> Custnr        Money
> 100            40
> 100            50
> 150            10
> 150            40

> TableTwo
> Custnr        Quantity
> 100             100
> 100              200
> 150             50
> 150             60

> As result from the SELECT statement I want the following

> Custnr        Money         Quantity
> 100             90                300
> 150             50                 110

> I tried the following
> SELET Custnr, SUM(DISTINCT Money), SUM(DISTINCT Quantity) FROM TableOne,
> INNER JOIN TableTwo ON TableOne.Custnr = TableTwo.Custnr.
> This works, but I think it's not the proper way (performance etc) to do it
> like this.

> Is this the correct way of selecting the records, or is there a more
> efficient way?

> Thx.

> Robbert