Re: Subquery with multiple rows

On Tue, Jun 14, 2016 at 2:22 AM, Weiss, Jörg <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.