Thread: Subquery with multiple rows

Subquery with multiple rows

From
Weiss, Jörg
Date:
<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US">Hi all!</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">How can I outsource a subquery?</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">An Example:</span><p
class="MsoNormal"><spanlang="EN-US">SELECT DISTINCT a.*,</span><p class="MsoNormal"><span lang="EN-US">(       SELECT
SUM(std)</span><p class="MsoNormal"><span lang="EN-US">        FROM all_std </span><p class="MsoNormal"><span
lang="EN-US">        WHERE(a.status <=5 AND status = 5) </span><p class="MsoNormal"><span lang="EN-US">        AND
foerd_idIN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')  </span><p class="MsoNormal"><span lang="EN-US">) AS
done_std</span><pclass="MsoNormal"><span lang="EN-US">(       SELECT SUM(anerk_std) </span><p class="MsoNormal"><span
lang="EN-US">        FROMall_std</span><p class="MsoNormal"><span lang="EN-US">        WHERE (a.status >5 AND status
<5)</span><p class="MsoNormal"><span lang="EN-US">        AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id
='55896')</span><p class="MsoNormal"><span lang="EN-US">) AS accepted_std</span><p class="MsoNormal"><span
lang="EN-US">FROMtable_a a </span><p class="MsoNormal"><span lang="EN-US">WHERE a.mass_id = '55896';</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">The sub-subquery is “SELECT
foerd_idFROM foerds WHERE mass_id = '55896')” an delivers more than one row.</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">Now I want to run the subquery only one time. </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I tried this: </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">SELECT DISTINCT a.*,</span><p
class="MsoNormal"><spanlang="EN-US">(       SELECT SUM(std) </span><p class="MsoNormal"><span lang="EN-US">        FROM
all_std</span><p class="MsoNormal"><span lang="EN-US">        WHERE (a.status <=5 AND status = 5) </span><p
class="MsoNormal"><spanlang="EN-US">        AND foerd_id IN (f.foerd_id)   </span><p class="MsoNormal"><span
lang="EN-US">)AS done_std,</span><p class="MsoNormal"><span lang="EN-US">(       SELECT SUM(anerk_std) </span><p
class="MsoNormal"><spanlang="EN-US">        FROM all_std</span><p class="MsoNormal"><span lang="EN-US">        WHERE
(a.status>5 AND status < 5)</span><p class="MsoNormal"><span lang="EN-US">        AND foerd_id IN
(f.foerd_id)</span><pclass="MsoNormal"><span lang="EN-US">) AS accepted_std</span><p class="MsoNormal"><span
lang="EN-US">FROMtable_a a,</span><p class="MsoNormal"><span lang="EN-US">(SELECT foerd_id FROM foerds WHERE mass_id =
'55896') f</span><p class="MsoNormal"><span lang="EN-US">WHERE a.mass_id = '55896';</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">But the result is not correct because I got one row for
everyrow of the of “f.foerd_id”.</span><p class="MsoNormal"><span lang="EN-US">Allowed is only one row.</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">How must the SQL looks like to get
theright result?</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal">Regards…<pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE"> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">Mitfreundlichen Grüßen</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">J.
Weiss</span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE"> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">EntwicklerSachgebiet GEW /
e-Lösungen</span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">E-Mail:<a
href="mailto:j.weiss@dvz-mv.de"><spanstyle="color:blue">j.weiss@dvz-mv.de</span></a></span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">Telefon:+49 (3 85) 48 00
351</span><pclass="MsoNormal" style="margin-bottom:12.0pt"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">Telefax:+49 (3 85) 48 00 98 351<br
/>Internet: <a href="http://www.dvz-mv.de/"><span style="color:blue">www.dvz-mv.de</span></a><br /><br /></span><span
style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">_____________________________________</span><span
style="color:#1F497D;mso-fareast-language:DE"><br/></span><span
style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">DVZ
Datenverarbeitungszentrum<br/> Mecklenburg-Vorpommern GmbH<br /> Lübecker Str. 283 - 19059 Schwerin</span><span
style="color:#1F497D;mso-fareast-language:DE"><br/></span><span
style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">Sitzder Gesellschaft:
Schwerin| Eintrag im Handelsregister: HRB 187 / Amtsgericht Schwerin<br /> Geschäftsführer: Hubert Ludwig |
Aufsichtsratsvorsitzender:Staatssekretär Peter Bäumer</span><span style="color:#1F497D;mso-fareast-language:DE"><br
/></span><span
style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">_____________________________________</span><span
style="color:#1F497D;mso-fareast-language:DE"></span><pclass="MsoNormal"> </div> 

Re: Subquery with multiple rows

From
Benjamin Dietrich
Date:
Hi Jörg,

> How can I outsource a subquery?

maybe you could try to use "Common Table Expressions" and do something like:

WITH foerd_id AS      (SELECT foerd_id FROM foerds WHERE mass_id = '55896')
SELECT DISTINCT a.*,
(       SELECT SUM(std)       FROM all_std       WHERE (a.status <=5 AND status = 5)         AND foerd_id IN (SELECT *
FROMfoerd_id) 

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

or with some more “outsourcing”:

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


Or maybe unnesting both aggregate subqueries in order to merge them and make use of FILTER-clauses
(https://www.postgresql.org/docs/9.5/static/sql-expressions.html#SYNTAX-AGGREGATES)might be a nice option. Something
like:

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

Regards,  Benjamin





Re: Subquery with multiple rows

From
Weiss, Jörg
Date:
Hi Benjamin!

Thank You! 

All Versions are working. 
I Think i will use the Version with FILTER clause.
The bad thing is, all versions are not much faster than my old version.

Regards, 
Jörg

-----Ursprüngliche Nachricht-----
Von: Benjamin Dietrich [mailto:b.dietrich@uni-tuebingen.de] 
Gesendet: Dienstag, 14. Juni 2016 11:39
An: Weiss, Jörg <J.Weiss@dvz-mv.de>
Cc: pgsql-sql@postgresql.org
Betreff: Re: [SQL] Subquery with multiple rows

Hi Jörg,

> How can I outsource a subquery?

maybe you could try to use "Common Table Expressions" and do something like:

WITH foerd_id AS      (SELECT foerd_id FROM foerds WHERE mass_id = '55896') SELECT DISTINCT a.*,
(       SELECT SUM(std)       FROM all_std       WHERE (a.status <=5 AND status = 5)         AND foerd_id IN (SELECT *
FROMfoerd_id)
 

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

or with some more “outsourcing”:

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


Or maybe unnesting both aggregate subqueries in order to merge them and make use of FILTER-clauses
(https://www.postgresql.org/docs/9.5/static/sql-expressions.html#SYNTAX-AGGREGATES)might be a nice option. Something
like:

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

Regards, Benjamin



Re: Subquery with multiple rows

From
"David G. Johnston"
Date:
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)​
 
​AS my_sums​
WHERE a.mass_id = $1

David J.

Re: Subquery with multiple rows

From
Weiss, Jörg
Date:

 

 

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)​

 

​AS my_sums​

WHERE a.mass_id = $1

 

David J.

 

OK,

 

works fine! Thank You

 

Jörg

 

Re: Subquery with multiple rows

From
Mohd Hazmin Zailan
Date:

Hi,

Why don't you join table_a and all_std and leave " WHERE (a.status <=5 AND status = 5 AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896') " do fix until got the result you wanted. Sorry not showing you in detail, just to give idea if it is logic for your solution.

Thanks

On Jun 14, 2016 2:22 PM, "Weiss, Jörg" <J.Weiss@dvz-mv.de> wrote:

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.weiss@dvz-mv.de

Telefon: +49 (3 85) 48 00 351

Telefax: +49 (3 85) 48 00 98 351
Internet: 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

_____________________________________