Help needed migrating query from MySQL

I'm new to Oracle and am migrating an app from MySQL that uses the following query:

select su.*,ih.*,sum(ih.invtotal-ih.amtpaid) as total from
subscriber su, inv_header ih
where su.subid=ih.subid and ih.invtotal > ih.amtpaid
group by su.subid;

This works find in MySQL but gives an ORA-00979: not a GROUP BY expression with 10g.

Any help would be appreciated.

Kevin

All columns in the select list must either be included in the group by clause or the argument of an aggregate function.

You says that it works fine in Mysql but actually you will get unpredictable results.

What would be the appropriate way to express this in oracle since I can't list all items in the group by? The subscriber table has a one-to-many relationship with inv_header and I'm wanting to select the subscriber info + the value of the sum aggregate from inv_header.

Thanks

Well, assume that your data looks like

T:

A B C
-----
1 3 3
1 2 4
1 4 5

When doing the query



which value from the column b should be returned? As there is no way of deciding a good DBMS will say that the query is bad.

If you say it does not matter which value you get I don't see why you include the column in the select list at all. You shouldn't use * either (in my opinion), specify the columns you want explicitly.

So you can either omit the column from the select list or apply a aggregate function to it, eg max or min.

Mysql can give any of the values in the B column and the result may differ from time to time which is not desirable.

Thanks for the info. I was able to solve my problem by using a sub-select as follows:

select su.subid,su.name,ih.total
from subscriber su,
(select subid,sum(invtotal-amtpaid) total
from inv_header
where invtotal > amtpaid
group by subid) ih
where su.paymethod=2
and su.subid=ih.subid
order by su.subid;

This seems to work perfectly.