General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>SELECT sales.sal_amt, SUM(refund.ref_amt), customer.cust_num, >customer.cust_name ;
>FROM sales, customer, refund ;
>INTO CURSOR rpt013a ;
>WHERE customer.cust_num = sales.cust_num
>AND refund.cust_num = customer.cust_num
I can not just do a join of the Sales and Refund tables. I get repeated rows of values. Say the Sales table has two records for a given cust_num: sal_amt = 200 and sal_amt = 300. And the Refund table has three rows for the same cust_num: ref_amt = 50, ref_amt = 60, and ref_amt = 70 I end up with a result set of
sal_amt ref_amt cust_num
200 50 xx1
300 50 xx1
200 60 xx1
300 60 xx1
200 70 xx1
300 70 xx1
So obviously the ref_sum in the SQL will = 360 which is incorrect and the sal_amt sum in the report will also be incorrect.
Maybe I need to put my result set to a table, then add a column, then somehow put the sum of the ref_amt into that column?
Brenda
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only