Re: Again:How to create the SELECT script?

    The original suggestion is correct in what you are looking for, but with two minor alterations. You will need to make the join an outer join so that you will include the ACC_NOs that aren't in the detail table, and in doing that, you would need to return a big fat ZERO for those ACC_NOs that weren't in the detail table. I think what you are looking of is this:

[Quoted]         select t1.ACC_NO, t1.ACC_NM, sum ( nvl ( t2.AMT ) ) Amount
          from table2 t2, table1 t1
         where t1.ACC_NO = t2.ACC_NO (+)
          group by t.ACC_NO, t1.ACC_NM
          order by t.ACC_NO;

    Of course, if you don't want the ZERO, then just leave off the nvl  .... ) part of the statement. Hope this helps.

                                                                    Shane.

Violin wrote in message <35d83412.2532343_at_news.twsc.pouchen.com.tw>...
>Hello,
>I think there is some misunderstanding in my last post.
>(My database is Oracle 7.3)
>
>I wonder how to return the result:
>List all rows of master TABLE1,and count sum(AMT) of TABLE2.
>But if I use the clause:WHERE TABLE1.ACC_NO=TABLE2.ACC_NO,
>then I just could get the rows exist in both TABLE1 & TABLE2.
>So my purpose is not only count sum(AMT) of TABLE2 for each ACC_NO,
>but also list all the ACC_NO in master TABLE1 whether the ACC_NO
>exists in TABLE2.
>
>ACC_NO ACC_NM TOTAL
>---------------------------------------------------
>10 Training cost 500
>20 Traffic cost 200
>30 Medical cost 5000
>"40 Sale cost " -> the 2 ACC_NO
>
>50 Manage cost 1000 don't exist in TABLE2,
>"60 Telecom cost " ->but I want to see!
Received on Thu Aug 13 1998 - 04:57:28 CEST