Subquery with multiple rows

Hi all!

How can I outsource a subquery?

An Example:
SELECT DISTINCT a.*,
( SELECT SUM(std)
FROM all_std
WHERE (a.status <=5 AND status = 5)
AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')
) AS done_std
( SELECT SUM(anerk_std)
FROM all_std
WHERE (a.status >5 AND status < 5)
AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')
) AS accepted_std
FROM table_a a
WHERE a.mass_id = '55896';

The sub-subquery is "SELECT foerd_id FROM foerds WHERE mass_id = '55896')" an delivers more than one row.

Now I want to run the subquery only one time.

I tried this:

SELECT DISTINCT a.*,
( SELECT SUM(std)
FROM all_std
WHERE (a.status <=5 AND status = 5)
AND foerd_id IN (f.foerd_id)
) AS done_std,
( SELECT SUM(anerk_std)
FROM all_std
WHERE (a.status >5 AND status < 5)
AND foerd_id IN (f.foerd_id)
) AS accepted_std
FROM table_a a,
(SELECT foerd_id FROM foerds WHERE mass_id = '55896') f
WHERE a.mass_id = '55896';

But the result is not correct because I got one row for every row of the of "f.foerd_id".
Allowed is only one row.

How must the SQL looks like to get the right result?

Regards...

Mit freundlichen Grüßen
J. Weiss

Entwickler Sachgebiet GEW / e-Lösungen
E-Mail: j(dot)weiss(at)dvz-mv(dot)de<mailto:j(dot)weiss(at)dvz-mv(dot)de>
Telefon: +49 (3 85) 48 00 351
Telefax: +49 (3 85) 48 00 98 351
Internet: www.dvz-mv.de<http://www.dvz-mv.de/>

_____________________________________
DVZ Datenverarbeitungszentrum
Mecklenburg-Vorpommern GmbH
Lübecker Str. 283 - 19059 Schwerin
Sitz der Gesellschaft: Schwerin | Eintrag im Handelsregister: HRB 187 / Amtsgericht Schwerin
Geschäftsführer: Hubert Ludwig | Aufsichtsratsvorsitzender: Staatssekretär Peter Bäumer
_____________________________________