[SQL] Subquery with multiple rows

Weiss, Jörg

unread,

Jun 14, 2016, 2:22:24 AM6/14/16

to

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.w...@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
_____________________________________

Benjamin Dietrich

unread,

Jun 14, 2016, 5:40:05 AM6/14/16

to

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 * FROM foerd_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

--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Weiss, Jörg

unread,

Jun 14, 2016, 8:39:18 AM6/14/16

to

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.die...@uni-tuebingen.de]
Gesendet: Dienstag, 14. Juni 2016 11:39
An: Weiss, Jörg <J.W...@dvz-mv.de>
Cc: pgsq...@postgresql.org
Betreff: Re: [SQL] Subquery with multiple rows

David G. Johnston

unread,

Jun 14, 2016, 9:04:39 AM6/14/16

to

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

Weiss, Jörg

unread,

Jun 14, 2016, 11:13:26 AM6/14/16

to

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

On Tue, Jun 14, 2016 at 2:22 AM, Weiss, Jörg <J.W...@dvz-mv.de> wrote:

David J.

OK,

works fine! Thank You

Jörg

Mohd Hazmin Zailan

unread,

Jun 14, 2016, 11:42:46 AM6/14/16

to

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.W...@dvz-mv.de> wrote:

Hi all!

How can I outsource a subquery?

An Example:

SELECT DISTINCT a.*,

( SELECT SUM(std)

Regards…