mutiple records returned.

[xq]

i have to select data from database and it envovles huge number of tables. each records will be returned 11 times . after i used distinct function on ID, it returns the result as:

ID amounts flow_no ...
1 40 1
2 20 1
2 25 2
2 10 3
3 10 1
3 15 2
4 20 1

code:
select distinct(deals.ID),cflows.amounts, events.flow_no, ...

from deals, cflows, events, ...

where ....

it pretty close to the result i want, then i have to sum up the amounts which have to be grouped by ID, but the problem is once i used group by, it will sum up all the record include those mutiple records. would i be able to only sum up the records as shown above. thanks a lot!

 

[tlbroadbent]

You should use DISTINCT for the entire row.

select distinct deals.ID, cflows.amounts, events.flow_no, ...

NOTE: Using DISTINCT in the select clause will return distinct rows. A row is distinct if one column is different.

Often, DISTINCT will not return the result you need. For example, if you only want one row per ID, you need to aggregate the remaining columns in the select list. Use aggregate functions such as MIN, MAX, SUM, AVG, etc. along with a group by clause on the ID column.

Sometimes, you must use subqueries to determine which rows to select. For example, you may need to return the row containing the data form the latest date for each ID.

In these last two examples, DISTINCT will not be adequate. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[xq]

but how do i sum up the distinct rows grouped by ID? thank u!

 

[xq]

sorry, just fixed the problem, thanks a lot!