Re: Subquery with multiple rows

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(dot)dietrich(at)uni-tuebingen(dot)de]
Gesendet: Dienstag, 14. Juni 2016 11:39
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

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