sum() on join with different number of columns
A user on Informix SE 7.25 found that joining tab1 to tab2 (one product row matching many tax rows) multiplied tab1's cost across the join, so sum(tab1.cost) returned 16000 instead of 2000. Suggested fixes: a correlated subquery (Art Kagel) selecting tab1.cost with an inline SUM over tab2; outer joins or a UNION (Bryce), which the poster found ran forever though UNION looked usable; and aggregating each table first in views (or grouping by tab1.col/cost rather than summing it) before joining (bozon). No definitive confirmation of which approach the poster adopted is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm having trouble getting two columns to sum properly because they do
not have the same number of rows returned by the select. I'm using
Informix SE 7.25.UC6R1.
select sum(tab1.cost), sum(tab2.cost)
from tab1, tab2
where tab1.col = tab2.col
result:
(sum) (sum)
16000 807.90
tab2 has more rows than tab1 so tab1 is not summing correctly, tab1
should sum to 2000 not 16000. If I remove the sum() the data looks
like this:
select tab1.cost, tab2.cost
from tab1, tab2
where tab1.col = tab2.col
tab1.cost tab2.cost
2000 8.75
2000 150.00
2000 244.00
2000 1.20
2000 8.75
2000 150.00
2000 244.00
2000 1.20
There is really only one row with a value of 2000 in tab1.cost, but
the select is putting a value in for each row of tab2.cost which is
way the sum doesn't give the desired result. How can I make this work?
Try this one:
SELECT tab1.cost, (
SELECT SUM(tab2.cost) FROM tab2 wheretab1.col = tab2.col
)
FROM tab1;
Tested:
> select * from tab1;
one two
1 1000
2 500
2 row(s) retrieved.
> select * from tab2;
one two
1 200
1 200
1 200
1 200
2 35
2 35
6 row(s) retrieved.
> select tab1.two, (select sum(tab2.two) from tab2 where tab1.one =
tab2.one)
> from tab1;
two (expression)
1000 800
500 70
2 row(s) retrieved.
>
Art
On Tue, Nov 25, 2008 at 10:05 AM, cd <c320sky@gmail.com> wrote:
> I'm having trouble getting two columns to sum properly because they do
> not have the same number of rows returned by the select. I'm using
> Informix SE 7.25.UC6R1.
>
> select sum(tab1.cost), sum(tab2.cost)
> from tab1, tab2
> where tab1.col = tab2.col
>
> result:
> (sum) (sum)
> 16000 807.90
>
> tab2 has more rows than tab1 so tab1 is not summing correctly, tab1
> should sum to 2000 not 16000. If I remove the sum() the data looks
> like this:
>
> select tab1.cost, tab2.cost
> from tab1, tab2
> where tab1.col = tab2.col>
> tab1.cost tab2.cost
> 2000 8.75
> 2000 150.00
> 2000 244.00
> 2000 1.20
> 2000 8.75
> 2000 150.00
> 2000 244.00
> 2000 1.20
>
> There is really only one row with a value of 2000 in tab1.cost, but
> the select is putting a value in for each row of tab2.cost which is
> way the sum doesn't give the desired result. How can I make this work?
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
My initial thoughts on this were:
Is there always a match between tab1.col values to tab2.col values, only
tab2 has additional rows as well?
select sum(tab1.cost), sum(tab2.cost)
from tab2, outer tab1
where tab1.col = tab2.col
If could be missing col values on both tables, then is getting it as two
rows any use?
select sum(tab2.cost)
from tab2, outer tab1
where tab1.col = tab2.col
UNION
select sum(tab1.cost)
from tab1, outer tab2
where tab1.col = tab2.col
(but I don't have se7.25, appeared to work with ids11.5)
Regards,
Bryce Stenberg.
"cd" <c320sky@gmail.com> wrote in message
news:fd295a62-a8be-4df6-84af-2dc1b71c4a3f@k19g2000yqg.googlegroups.com...
> I'm having trouble getting two columns to sum properly because they do
> not have the same number of rows returned by the select. I'm using
> Informix SE 7.25.UC6R1.
>
> select sum(tab1.cost), sum(tab2.cost)
> from tab1, tab2
> where tab1.col = tab2.col
>
> result:
> (sum) (sum)
> 16000 807.90
>
> tab2 has more rows than tab1 so tab1 is not summing correctly, tab1
> should sum to 2000 not 16000. If I remove the sum() the data looks
> like this:
>
> select tab1.cost, tab2.cost
> from tab1, tab2
> where tab1.col = tab2.col>
> tab1.cost tab2.cost
> 2000 8.75
> 2000 150.00
> 2000 244.00
> 2000 1.20
> 2000 8.75
> 2000 150.00
> 2000 244.00
> 2000 1.20
>
> There is really only one row with a value of 2000 in tab1.cost, but
> the select is putting a value in for each row of tab2.cost which is
> way the sum doesn't give the desired result. How can I make this work?
On Nov 25, 4:39 pm, "Bryce S." <br...@hrnz.co.nz> wrote:
> My initial thoughts on this were:
> Is there always a match between tab1.col values to tab2.col values, only
> tab2 has additional rows as well?
>
> select sum(tab1.cost), sum(tab2.cost)
> from tab2, outer tab1
> where tab1.col = tab2.col
>
> If could be missing col values on both tables, then is getting it as two
> rows any use?
>
> select sum(tab2.cost)
> from tab2, outer tab1
> where tab1.col = tab2.col
> UNION
> select sum(tab1.cost)
> from tab1, outer tab2
> where tab1.col = tab2.col
>
> (but I don't have se7.25, appeared to work with ids11.5)
>
> Regards,
> Bryce Stenberg.
>
> "cd" <c320...@gmail.com> wrote in message
>
> news:fd295a62-a8be-4df6-84af-2dc1b71c4a3f@k19g2000yqg.googlegroups.com...
>
> > I'm having trouble getting two columns to sum properly because they do
> > not have the same number of rows returned by the select. I'm using
> > Informix SE 7.25.UC6R1.
>
> > select sum(tab1.cost), sum(tab2.cost)
> > from tab1, tab2
> > where tab1.col = tab2.col
>
> > result:
> > (sum) (sum)
> > 16000 807.90
>
> > tab2 has more rows than tab1 so tab1 is not summing correctly, tab1
> > should sum to 2000 not 16000. If I remove the sum() the data looks
> > like this:
>
> > select tab1.cost, tab2.cost
> > from tab1, tab2
> > where tab1.col = tab2.col>
> > tab1.cost tab2.cost
> > 2000 8.75
> > 2000 150.00
> > 2000 244.00
> > 2000 1.20
> > 2000 8.75
> > 2000 150.00
> > 2000 244.00
> > 2000 1.20
>
> > There is really only one row with a value of 2000 in tab1.cost, but
> > the select is putting a value in for each row of tab2.cost which is
> > way the sum doesn't give the desired result. How can I make this work?
With the outer join the query runs forever. I may just use a union
and not add the two values together, it seems it may be the best
solution.
On Nov 25, 10:05 am, cd <c320...@gmail.com> wrote:
> I'm having trouble getting two columns to sum properly because they do
> not have the same number of rows returned by the select. I'm using
> Informix SE 7.25.UC6R1.
>
> select sum(tab1.cost), sum(tab2.cost)
> from tab1, tab2
> where tab1.col = tab2.col
>
> result:
> (sum) (sum)
> 16000 807.90
>
> tab2 has more rows than tab1 so tab1 is not summing correctly, tab1
> should sum to 2000 not 16000. If I remove the sum() the data looks
> like this:
>
> select tab1.cost, tab2.cost
> from tab1, tab2
> where tab1.col = tab2.col>
> tab1.cost tab2.cost
> 2000 8.75
> 2000 150.00
> 2000 244.00
> 2000 1.20
> 2000 8.75
> 2000 150.00
> 2000 244.00
> 2000 1.20
>
> There is really only one row with a value of 2000 in tab1.cost, but
> the select is putting a value in for each row of tab2.cost which is
> way the sum doesn't give the desired result. How can I make this work?
I am not sure why you are joining these two tables. What are you
trying to accomplish?
Well here is my solution. But before I give it you will have to read
through 2 of my favorite rants. You can search this news group if you
want to see how often I go off on these two things.
First include complete DDL of your sample so anyone who wants to play
along can do so quickly, without recreating the wheel. You obviously
have a nice sample set up so why don't you include it. Lucky I am in a
good mood.
Secondly: VIEWS, VIEWS, VIEWS. Doesn't anyone ever use them? It is
quite simple to solve with two quick views, which is all that inline
view solutions do:
So here is my solution with complete DDL and using two views. You are
trying to group col column and then sum. But joining goes first in the
SQL. So you have to force it to group first and then join. This
requires a view or as people like to do in the future (later versions)
inline views.
create table tab1(
col int,
cost money
) ;
create table tab2(
col int,
cost money
) ;
insert into tab1 values (1,2000);
insert into tab2 values (1, 8.75) ;
insert into tab2 values (1, 150.00) ;
insert into tab2 values (1, 244.00) ;
insert into tab2 values (1, 1.20) ;
insert into tab2 values (1, 8.75) ;
insert into tab2 values (1, 150.00) ;
insert into tab2 values (1, 244.00) ;
insert into tab2 values (1, 1.20) ;
create view tab1_sum(col, sum_cost) as
select col, sum(cost) from tab1 group by 1;
create view tab2_sum(col, sum_cost) as
select col, sum(cost) from tab2 group by 1;
select sum(tab1.cost), sum(tab2.cost)
from tab1, tab2
where tab1.col = tab2.col ;
select
tab1_sum.col,
tab1_sum.sum_cost,
tab2_sum.sum_cost
from
tab1_sum,
tab2_sum
where
tab1_sum.col = tab2_sum.col
;
Your bad SQL:
(sum) (sum)
$16000.00 $807.90
My corrected SQL:
col sum_cost sum_cost
1 $2000.00 $807.90
On Nov 26, 9:22 am, bozon <cur...@crowson1.com> wrote:
> On Nov 25, 10:05 am, cd <c320...@gmail.com> wrote:
>
>
>
> > I'm having trouble getting two columns to sum properly because they do
> > not have the same number of rows returned by the select. I'm using
> > Informix SE 7.25.UC6R1.
>
> > select sum(tab1.cost), sum(tab2.cost)
> > from tab1, tab2
> > where tab1.col = tab2.col
>
> > result:
> > (sum) (sum)
> > 16000 807.90
>
> > tab2 has more rows than tab1 so tab1 is not summing correctly, tab1
> > should sum to 2000 not 16000. If I remove the sum() the data looks
> > like this:
>
> > select tab1.cost, tab2.cost
> > from tab1, tab2
> > where tab1.col = tab2.col>
> > tab1.cost tab2.cost
> > 2000 8.75
> > 2000 150.00
> > 2000 244.00
> > 2000 1.20
> > 2000 8.75
> > 2000 150.00
> > 2000 244.00
> > 2000 1.20
>
> > There is really only one row with a value of 2000 in tab1.cost, but
> > the select is putting a value in for each row of tab2.cost which is
> > way the sum doesn't give the desired result. How can I make this work?
>
> I am not sure why you are joining these two tables. What are you
> trying to accomplish?
>
> Well here is my solution. But before I give it you will have to read
> through 2 of my favorite rants. You can search this news group if you
> want to see how often I go off on these two things.
>
> First include complete DDL of your sample so anyone who wants to play
> along can do so quickly, without recreating the wheel. You obviously
> have a nice sample set up so why don't you include it. Lucky I am in a
> good mood.
> Secondly: VIEWS, VIEWS, VIEWS. Doesn't anyone ever use them? It is
> quite simple to solve with two quick views, which is all that inline
> view solutions do:
>
> So here is my solution with complete DDL and using two views. You are
> trying to group col column and then sum. But joining goes first in the
> SQL. So you have to force it to group first and then join. This
> requires a view or as people like to do in the future (later versions)
> inline views.
>
> create table tab1(
> col int,
> cost money
> ) ;>
> create table tab2(
> col int,
> cost money
> ) ;>
> insert into tab1 values (1,2000);>
> insert into tab2 values (1, 8.75) ;
> insert into tab2 values (1, 150.00) ;
> insert into tab2 values (1, 244.00) ;
> insert into tab2 values (1, 1.20) ;
> insert into tab2 values (1, 8.75) ;
> insert into tab2 values (1, 150.00) ;
> insert into tab2 values (1, 244.00) ;
> insert into tab2 values (1, 1.20) ;>
> create view tab1_sum(col, sum_cost) as
> select col, sum(cost) from tab1 group by 1> ;
>
> create view tab2_sum(col, sum_cost) as
> select col, sum(cost) from tab2 group by 1> ;
>
> select sum(tab1.cost), sum(tab2.cost)
> from tab1, tab2
> where tab1.col = tab2.col ;
>
> select
> tab1_sum.col,
> tab1_sum.sum_cost,
> tab2_sum.sum_cost
> from
> tab1_sum,
> tab2_sum
> where
> tab1_sum.col = tab2_sum.col
> ;
>
> Your bad SQL:
> (sum) (sum)
>
> $16000.00 $807.90
>
> My corrected SQL:
> col sum_cost sum_cost
>
> 1 $2000.00 $807.90
Also, I am curious about the why. Sometimes explaining why you want to
do something in your initial question can lead to better solutions
than just asking how to do something. Experts sometimes no things you
haven't even considered as solutions that would fit your problem
better than the solution that you were trying to get working correctly.
On Nov 26, 10:16 am, bozon <cur...@crowson1.com> wrote:
> On Nov 26, 9:22 am, bozon <cur...@crowson1.com> wrote:
>
>
>
> > On Nov 25, 10:05 am, cd <c320...@gmail.com> wrote:
>
> > > I'm having trouble getting two columns to sum properly because they do
> > > not have the same number of rows returned by the select. I'm using
> > > Informix SE 7.25.UC6R1.
>
> > > select sum(tab1.cost), sum(tab2.cost)
> > > from tab1, tab2
> > > where tab1.col = tab2.col
>
> > > result:
> > > (sum) (sum)
> > > 16000 807.90
>
> > > tab2 has more rows than tab1 so tab1 is not summing correctly, tab1
> > > should sum to 2000 not 16000. If I remove the sum() the data looks
> > > like this:
>
> > > select tab1.cost, tab2.cost
> > > from tab1, tab2
> > > where tab1.col = tab2.col>
> > > tab1.cost tab2.cost
> > > 2000 8.75
> > > 2000 150.00
> > > 2000 244.00
> > > 2000 1.20
> > > 2000 8.75
> > > 2000 150.00
> > > 2000 244.00
> > > 2000 1.20
>
> > > There is really only one row with a value of 2000 in tab1.cost, but
> > > the select is putting a value in for each row of tab2.cost which is
> > > way the sum doesn't give the desired result. How can I make this work?
>
> > I am not sure why you are joining these two tables. What are you
> > trying to accomplish?
>
> > Well here is my solution. But before I give it you will have to read
> > through 2 of my favorite rants. You can search this news group if you
> > want to see how often I go off on these two things.
>
> > First include complete DDL of your sample so anyone who wants to play
> > along can do so quickly, without recreating the wheel. You obviously
> > have a nice sample set up so why don't you include it. Lucky I am in a
> > good mood.
> > Secondly: VIEWS, VIEWS, VIEWS. Doesn't anyone ever use them? It is
> > quite simple to solve with two quick views, which is all that inline
> > view solutions do:
>
> > So here is my solution with complete DDL and using two views. You are
> > trying to group col column and then sum. But joining goes first in the
> > SQL. So you have to force it to group first and then join. This
> > requires a view or as people like to do in the future (later versions)
> > inline views.
>
> > create table tab1(
> > col int,
> > cost money
> > ) ;>
> > create table tab2(
> > col int,
> > cost money
> > ) ;>
> > insert into tab1 values (1,2000);>
> > insert into tab2 values (1, 8.75) ;
> > insert into tab2 values (1, 150.00) ;
> > insert into tab2 values (1, 244.00) ;
> > insert into tab2 values (1, 1.20) ;
> > insert into tab2 values (1, 8.75) ;
> > insert into tab2 values (1, 150.00) ;
> > insert into tab2 values (1, 244.00) ;
> > insert into tab2 values (1, 1.20) ;>
> > create view tab1_sum(col, sum_cost) as
> > select col, sum(cost) from tab1 group by 1> > ;
>
> > create view tab2_sum(col, sum_cost) as
> > select col, sum(cost) from tab2 group by 1> > ;
>
> > select sum(tab1.cost), sum(tab2.cost)
> > from tab1, tab2
> > where tab1.col = tab2.col ;
>
> > select
> > tab1_sum.col,
> > tab1_sum.sum_cost,
> > tab2_sum.sum_cost
> > from
> > tab1_sum,
> > tab2_sum
> > where
> > tab1_sum.col = tab2_sum.col
> > ;
>
> > Your bad SQL:
> > (sum) (sum)
>
> > $16000.00 $807.90
>
> > My corrected SQL:
> > col sum_cost sum_cost
>
> > 1 $2000.00 $807.90
>
> Also, I am curious about the why. Sometimes explaining why you want to
> do something in your initial question can lead to better solutions
> than just asking how to do something. Experts sometimes no things you
> haven't even considered as solutions that would fit your problem
> better than the solution that you were trying to get working correctly.
I didn't post the actual sql I'm working with I thought it would be
easier to simplify it. What I am trying to do is select the sum of
the cost of a product, which is what the $2000 from tab1.cost in this
example represents. I need the sum() because there may be more than
one product. Then I want to add to that the sum of the tax on the
product and since there is more than one tax per product, tab2.cost
(tax) will always have more rows for each row in tab1.cost. Also I'm
using this sql in a 4gl program.
On Nov 26, 12:45 pm, cd <c320...@gmail.com> wrote:
> On Nov 26, 10:16 am, bozon <cur...@crowson1.com> wrote:
>
>
>
> > On Nov 26, 9:22 am, bozon <cur...@crowson1.com> wrote:
>
> > > On Nov 25, 10:05 am, cd <c320...@gmail.com> wrote:
>
> > > > I'm having trouble getting two columns to sum properly because they do
> > > > not have the same number of rows returned by the select. I'm using
> > > > Informix SE 7.25.UC6R1.
>
> > > > select sum(tab1.cost), sum(tab2.cost)
> > > > from tab1, tab2
> > > > where tab1.col = tab2.col
>
> > > > result:
> > > > (sum) (sum)
> > > > 16000 807.90
>
> > > > tab2 has more rows than tab1 so tab1 is not summing correctly, tab1
> > > > should sum to 2000 not 16000. If I remove the sum() the data looks
> > > > like this:
>
> > > > select tab1.cost, tab2.cost
> > > > from tab1, tab2
> > > > where tab1.col = tab2.col>
> > > > tab1.cost tab2.cost
> > > > 2000 8.75
> > > > 2000 150.00
> > > > 2000 244.00
> > > > 2000 1.20
> > > > 2000 8.75
> > > > 2000 150.00
> > > > 2000 244.00
> > > > 2000 1.20
>
> > > > There is really only one row with a value of 2000 in tab1.cost, but
> > > > the select is putting a value in for each row of tab2.cost which is
> > > > way the sum doesn't give the desired result. How can I make this work?
>
> > > I am not sure why you are joining these two tables. What are you
> > > trying to accomplish?
>
> > > Well here is my solution. But before I give it you will have to read
> > > through 2 of my favorite rants. You can search this news group if you
> > > want to see how often I go off on these two things.
>
> > > First include complete DDL of your sample so anyone who wants to play
> > > along can do so quickly, without recreating the wheel. You obviously
> > > have a nice sample set up so why don't you include it. Lucky I am in a
> > > good mood.
> > > Secondly: VIEWS, VIEWS, VIEWS. Doesn't anyone ever use them? It is
> > > quite simple to solve with two quick views, which is all that inline
> > > view solutions do:
>
> > > So here is my solution with complete DDL and using two views. You are
> > > trying to group col column and then sum. But joining goes first in the
> > > SQL. So you have to force it to group first and then join. This
> > > requires a view or as people like to do in the future (later versions)
> > > inline views.
>
> > > create table tab1(
> > > col int,
> > > cost money
> > > ) ;>
> > > create table tab2(
> > > col int,
> > > cost money
> > > ) ;>
> > > insert into tab1 values (1,2000);>
> > > insert into tab2 values (1, 8.75) ;
> > > insert into tab2 values (1, 150.00) ;
> > > insert into tab2 values (1, 244.00) ;
> > > insert into tab2 values (1, 1.20) ;
> > > insert into tab2 values (1, 8.75) ;
> > > insert into tab2 values (1, 150.00) ;
> > > insert into tab2 values (1, 244.00) ;
> > > insert into tab2 values (1, 1.20) ;>
> > > create view tab1_sum(col, sum_cost) as
> > > select col, sum(cost) from tab1 group by 1> > > ;
>
> > > create view tab2_sum(col, sum_cost) as
> > > select col, sum(cost) from tab2 group by 1> > > ;
>
> > > select sum(tab1.cost), sum(tab2.cost)
> > > from tab1, tab2
> > > where tab1.col = tab2.col ;
>
> > > select
> > > tab1_sum.col,
> > > tab1_sum.sum_cost,
> > > tab2_sum.sum_cost
> > > from
> > > tab1_sum,
> > > tab2_sum
> > > where
> > > tab1_sum.col = tab2_sum.col
> > > ;
>
> > > Your bad SQL:
> > > (sum) (sum)
>
> > > $16000.00 $807.90
>
> > > My corrected SQL:
> > > col sum_cost sum_cost
>
> > > 1 $2000.00 $807.90
>
> > Also, I am curious about the why. Sometimes explaining why you want to
> > do something in your initial question can lead to better solutions
> > than just asking how to do something. Experts sometimes no things you
> > haven't even considered as solutions that would fit your problem
> > better than the solution that you were trying to get working correctly.
>
> I didn't post the actual sql I'm working with I thought it would be
> easier to simplify it. What I am trying to do is select the sum of
> the cost of a product, which is what the $2000 from tab1.cost in this
> example represents. I need the sum() because there may be more than
> one product. Then I want to add to that the sum of the tax on the
> product and since there is more than one tax per product, tab2.cost
> (tax) will always have more rows for each row in tab1.cost. Also I'm
> using this sql in a 4gl program.
What I wanted was the DDL that created your sample table and the
insert statements that populated it for your example. I created it
based on what you had presented. It would have saved me a little
typing and time.
My solution will work fine in a 4GL program just have someone create
the views one time in the database and give your user permission. You
then use the views just like you would the tables. Based on your
description above what I think you really want is this:
select col, tab1.cost, sum(tab2.cost) from tab1, tab2 where tab1.col =tab2.col group by 1, 2;
It seems like you don't need to sum the number from table 1 you just
have to group by it.
On Nov 26, 12:45 pm, cd <c320...@gmail.com> wrote:
> On Nov 26, 10:16 am, bozon <cur...@crowson1.com> wrote:
>
>
>
> > On Nov 26, 9:22 am, bozon <cur...@crowson1.com> wrote:
>
> > > On Nov 25, 10:05 am, cd <c320...@gmail.com> wrote:
>
> > > > I'm having trouble getting two columns to sum properly because they do
> > > > not have the same number of rows returned by the select. I'm using
> > > > Informix SE 7.25.UC6R1.
>
> > > > select sum(tab1.cost), sum(tab2.cost)
> > > > from tab1, tab2
> > > > where tab1.col = tab2.col
>
> > > > result:
> > > > (sum) (sum)
> > > > 16000 807.90
>
> > > > tab2 has more rows than tab1 so tab1 is not summing correctly, tab1
> > > > should sum to 2000 not 16000. If I remove the sum() the data looks
> > > > like this:
>
> > > > select tab1.cost, tab2.cost
> > > > from tab1, tab2
> > > > where tab1.col = tab2.col>
> > > > tab1.cost tab2.cost
> > > > 2000 8.75
> > > > 2000 150.00
> > > > 2000 244.00
> > > > 2000 1.20
> > > > 2000 8.75
> > > > 2000 150.00
> > > > 2000 244.00
> > > > 2000 1.20
>
> > > > There is really only one row with a value of 2000 in tab1.cost, but
> > > > the select is putting a value in for each row of tab2.cost which is
> > > > way the sum doesn't give the desired result. How can I make this work?
>
> > > I am not sure why you are joining these two tables. What are you
> > > trying to accomplish?
>
> > > Well here is my solution. But before I give it you will have to read
> > > through 2 of my favorite rants. You can search this news group if you
> > > want to see how often I go off on these two things.
>
> > > First include complete DDL of your sample so anyone who wants to play
> > > along can do so quickly, without recreating the wheel. You obviously
> > > have a nice sample set up so why don't you include it. Lucky I am in a
> > > good mood.
> > > Secondly: VIEWS, VIEWS, VIEWS. Doesn't anyone ever use them? It is
> > > quite simple to solve with two quick views, which is all that inline
> > > view solutions do:
>
> > > So here is my solution with complete DDL and using two views. You are
> > > trying to group col column and then sum. But joining goes first in the
> > > SQL. So you have to force it to group first and then join. This
> > > requires a view or as people like to do in the future (later versions)
> > > inline views.
>
> > > create table tab1(
> > > col int,
> > > cost money
> > > ) ;>
> > > create table tab2(
> > > col int,
> > > cost money
> > > ) ;>
> > > insert into tab1 values (1,2000);>
> > > insert into tab2 values (1, 8.75) ;
> > > insert into tab2 values (1, 150.00) ;
> > > insert into tab2 values (1, 244.00) ;
> > > insert into tab2 values (1, 1.20) ;
> > > insert into tab2 values (1, 8.75) ;
> > > insert into tab2 values (1, 150.00) ;
> > > insert into tab2 values (1, 244.00) ;
> > > insert into tab2 values (1, 1.20) ;>
> > > create view tab1_sum(col, sum_cost) as
> > > select col, sum(cost) from tab1 group by 1> > > ;
>
> > > create view tab2_sum(col, sum_cost) as
> > > select col, sum(cost) from tab2 group by 1> > > ;
>
> > > select sum(tab1.cost), sum(tab2.cost)
> > > from tab1, tab2
> > > where tab1.col = tab2.col ;
>
> > > select
> > > tab1_sum.col,
> > > tab1_sum.sum_cost,
> > > tab2_sum.sum_cost
> > > from
> > > tab1_sum,
> > > tab2_sum
> > > where
> > > tab1_sum.col = tab2_sum.col
> > > ;
>
> > > Your bad SQL:
> > > (sum) (sum)
>
> > > $16000.00 $807.90
>
> > > My corrected SQL:
> > > col sum_cost sum_cost
>
> > > 1 $2000.00 $807.90
>
> > Also, I am curious about the why. Sometimes explaining why you want to
> > do something in your initial question can lead to better solutions
> > than just asking how to do something. Experts sometimes no things you
> > haven't even considered as solutions that would fit your problem
> > better than the solution that you were trying to get working correctly.
>
> I didn't post the actual sql I'm working with I thought it would be
> easier to simplify it. What I am trying to do is select the sum of
> the cost of a product, which is what the $2000 from tab1.cost in this
> example represents. I need the sum() because there may be more than
> one product. Then I want to add to that the sum of the tax on the
> product and since there is more than one tax per product, tab2.cost
> (tax) will always have more rows for each row in tab1.cost. Also I'm
> using this sql in a 4gl program.
I answered based on the why, but I may need a little more detail to
make sure that my answer is correct. See my answer below and look at
your data to see if it works correctly.