Re: Subquery with multiple rows - Mailing list pgsql-sql

From Benjamin Dietrich
Subject Re: Subquery with multiple rows
Date
Msg-id A2A8A871-6747-4BC1-AFBC-A87B887BD62B@uni-tuebingen.de
Whole thread Raw
In response to Subquery with multiple rows  (Weiss, Jörg <J.Weiss@dvz-mv.de>)
Responses Re: Subquery with multiple rows
List pgsql-sql
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





pgsql-sql by date:

Previous
From: Weiss, Jörg
Date:
Subject: Subquery with multiple rows
Next
From: Weiss, Jörg
Date:
Subject: Re: Subquery with multiple rows