Re: Double aggregate problem

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.