From: nha <lyondif02@free.fr>
To: David Weilers <david@lionhead.nl>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Double aggregate problem
Date: Wed, 22 Jul 2009 19:45:58 +0200
Message-ID: <4A675056.9070501@free.fr> (raw)
In-Reply-To: <H00000670039e901.1248279381.lionhead2.lionhead.nl@MHS>
References: <H00000670039e901.1248279381.lionhead2.lionhead.nl@MHS>
Hello,
Le 22/07/09 18:16, David Weilers a écrit :
> Dear anyone,
>
> I have the following query:
>
> select v.id, array_to_string(array_accum(s.name),', ') as sector ,
> array_to_string(array_accum(p.name),', ') as provincie from tblvacature
> v, tblaccount a , tblvacaturesector vs, tblsector s ,
> tblvacatureprovincie vp, tblprovincie p where v.id = 11 and v.account =
> a.id and vs.vacature = v.id and s.id = vs.sector and vp.vacature = v.id
> and p.id = vp.provincie group by v.id, v.inserted order by v.inserted
> desc
> [...]
> If i leave out one aggregate, the result is as i expect (if I leave out
> 'provincie', sector gives):
> [...]
> I would like both array_accum returning only what they should and not
> doubles.
> [...]
According to your observation, the following query may match your need:
SELECT
t1.id, t1.sector, t2.provincie
FROM
(
SELECT
v.id, v.inserted,
array_to_string(array_accum(s.name),', ') AS sector
FROM
tblvacature v, tblaccount a, tblvacaturesector vs, tblsector s,
tblvacatureprovincie vp, tblprovincie p
WHERE
v.account = a.id and vs.vacature = v.id and s.id = vs.sector
and vp.vacature = v.id and p.id = vp.provincie
GROUP BY v.id, v.inserted
) AS t1
INNER JOIN
(
SELECT
v2.id,
array_to_string(array_accum(p2.name),', ') AS provincie
FROM
tblvacature v2, tblaccount a2, tblvacaturesector vs2, tblsector s2,
tblvacatureprovincie vp2, tblprovincie p2
WHERE
v2.account = a2.id and vs2.vacature = v2.id and s2.id = vs2.sector
and vp2.vacature = v2.id and p2.id = vp2.provincie
GROUP BY v2.id, v2.inserted
) AS t2
ON t1.id = t2.id
WHERE t1.id = 11
ORDER BY t1.inserted DESC
The query has been rewritten as an inner join between two subqueries
issued from the original one. WHERE and SORT clauses have been pulled
off so that subqueries are not too enough broken down. Other
optimization may be applied (eg. by using explicit joins between the
different tables and by checking appropriate indexes are set up).
Regards.
--
nha / Lyon / France.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lyondif02@free.fr, david@lionhead.nl
Subject: Re: Double aggregate problem
In-Reply-To: <4A675056.9070501@free.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox