Subquery Question

[Fabian Eschrich]

Hi,

currently I am changing a lot of queries in my app to port it from mysql to
valentina.

One of them loads a list of addresses and the sum of open accountings
(unpaid invoices) for each too.
In MySQL I did this with 

	select sum(amount) as amount,addresses.id,addresses.type as
addresstype,searchcode,name1,name2,customernumber
	from accounting
	left outer join addresses on accounting.addressid = addresses.id
	group by accounting.addressid

As I learned, this is not sql conform, because of mixing aggregate fields
and normal fields is not allowed.
So I thought the easiest way to solve this should be a simple subquery.

	select id,searchcode,type,name1,name2,customernumber,
	(SELECT SUM(Amount) from accounting where accounting.addressid = ID)
	from addresses

But on this one is not correct - gives me only 0,00 for everyone. Am I on
the right way to use a subquery?
I want to get this list with one query. Maybe I should use a different way?

Mit freundlichen Grüßen / kindly regards,

Fabian Eschrich
FAESCH Software

info at faesch.de
 
Fon:     +49 (0)37206 889293
Fax:     +49 (0)37206 889294
Skype:   feschrich
SIPgate: 9000760