Re: Subquery with multiple rows

Von: David G. Johnston [mailto:david(dot)g(dot)johnston(at)gmail(dot)com]
Gesendet: Dienstag, 14. Juni 2016 15:04
An: Weiss, Jörg <J(dot)Weiss(at)dvz-mv(dot)de>
Cc: pgsql-sql(at)postgresql(dot)org
Betreff: Re: [SQL] Subquery with multiple rows

On Tue, Jun 14, 2016 at 2:22 AM, Weiss, Jörg <J(dot)Weiss(at)dvz-mv(dot)de<mailto:J(dot)Weiss(at)dvz-mv(dot)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)​

​AS my_sums​
WHERE a.mass_id = $1

David J.

OK,

works fine! Thank You

Jörg