Von: David G. Johnston [mailto:david.g.johnston@gmail.com] Gesendet: Dienstag, 14. Juni 2016 15:04 An: Weiss, Jörg <J.Weiss@dvz-mv.de> Cc: pgsql-sql@postgresql.org Betreff: Re: [SQL] Subquery with multiple rows
On Tue, Jun 14, 2016 at 2:22 AM, Weiss, Jörg <J.Weiss@dvz-mv.de> wrote:
Hi all!
How can I outsource a subquery?
An Example:
SELECT DISTINCT a.*,
Lose the DISTINCT. DISTINCT is a code smell. In this case it is also pointless since a.* had better already be unique and its the only table in the query..
And, please don't top-post.
( 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?
The following should work on recent versions though you will need to play with the syntax.
SELECT a.*, my_sums.*
FROM table_a a
LATERAL JOIN (SELECT SUM(anerk_std) FILTER (...) AS accepted_std, SUM(std) FILTER (...) AS done_std FROM all_std WHERE all_std.mass_id = a.mass_id)