On 16 Oct 2003 at 3:30, james_027 wrote:

>I want to place the m.cust_id in the group by clause because I want
>to total_amount of invoice for each customer, but since in the view
>I want to be able to display the cust_name and cust_type I was force
>to include the d.cust_name and d.cust_type in the group by clause.
>My question is I am not sure if what iam doing is right ... I feel
>this is not the right way of doing it. What if I have other field in
>the customer table I want to view like customer credit limit,
>customer category and etc, does it mean that I have to put all that
>in the group by clause...

Yes, putting them into the group clause is a right way to go. But
there is another. If you need only one (summary) column from other
table you can use this construction:

select d.cust_id, d.cust_name, d.whatever_you_want,
(select sum(m.total_amount) from invoice m
where m.cust_id=d.cust_id) as total_amount
from customer d

SY, Dimitry Sibiryakov.