Thread: Double aggregate problem

Double aggregate problem

From

"David Weilers"

Date:

22 July 2009, 16:22:01

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

That currently produces the following output:

id |                      sector                       |
                  provincie
----+---------------------------------------------------+---------------
-------------------------------------------------------------------11 | Gaafjes, Eerder, Gaafjes, Eerder, Gaafjes,
Eerder| Noord-Holland,  

Noord-Holland, Limburg, Limburg, Oost-Vlaanderen, Oost-Vlaanderen

If i leave out one aggregate, the result is as i expect (if I leave out
'provincie', sector gives):

Gaafjes, Eeerder

Only two results.

I would like both array_accum returning only what they should and not
doubles.

Any help is appreciated.

--
Regards,

David Weilers

Re: Double aggregate problem

From

Peter Eisentraut

Date:

22 July 2009, 18:40:06

On Wednesday 22 July 2009 19:16:21 David Weilers wrote:
> 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
>
> That currently produces the following output:

No one is going to be able to reproduce that without the table definitions and 
data.

Re: Double aggregate problem

From

nha

Date:

22 July 2009, 19:01:55

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.

Re: Double aggregate problem

From

nha

Date:

23 July 2009, 01:25:15

Hello,

Le 22/07/09 20:04, Peter Eisentraut a écrit :
> On Wednesday 22 July 2009 19:16:21 David Weilers wrote:
>> 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
>>
>> That currently produces the following output:
> 
> No one is going to be able to reproduce that without the table definitions and 
> data.
> 

Peter Eisentraut is right... The rewritten query I suggested is only
derived from your original query and implicit data definitions
(including constraints).

I would like to point 2 things about that rewritten proposed query:
- Column 'inserted' is likely missing in the 2nd subquery and in the
global join condition;
- Duplicity or even multiplicity of value s.name and/or p.name may
result depending on the keys of tables (as implicitly mentioned by Peter
Eisentraut).

Regards.
--
nha / Lyon / France.

Re: Double aggregate problem

From

"David Weilers"

Date:

23 July 2009, 08:05:38

Hello,

I have the following tables:

CREATE TABLE tblvacature (id serial PRIMARY KEY,account int NOT NULL REFERENCES tblaccount (id) ON DELETE CASCADE,
title varchar(128),bedrijfsprofiel text,functieomschrijving text,functieeisen text,arbeidsvoorwaarden textoverig
text,sollicitatieinfotext, 
inserted timestamp DEFAULT now()
);

CREATE TABLE tblvacaturesector ( vacature int NOT NULL REFERENCES
tblvacature (id) ON DELETE CASCADE, sector int NOT NULL REFERENCES
tblsector (id) ON DELETE CASCADE );
select * from tblvacaturesector where vacature = 11;vacature | sector
----------+--------      11 |      5      11 |      2

CREATE TABLE tblvacatureprovincie ( vacature int NOT NULL REFERENCES
tblvacature (id) ON DELETE CASCADE, provincie int NOT NULL REFERENCES
tblprovincie (id) ON DELETE CASCADE );
select * from tblvacatureprovincie where vacature = 11;vacature | provincie
----------+-----------      11 |         7      11 |         1      11 |         8

CREATE TABLE tblprovincie (id serial PRIMARY KEY,land int NOT NULL REFERENCES tblland (id) ON DELETE RESTRICT,
name varchar(128)
);
select * from tblprovincie;id | land |      name
----+------+----------------- 1 |    1 | Noord-Holland 2 |    1 | Zuid-Holland 3 |    1 | Groningen 4 |    2 | Brabant
5|    1 | Utrecht 6 |    2 | Antwerpen 7 |    2 | Limburg 8 |    2 | Oost-Vlaanderen 

CREATE TABLE tblsector (id serial PRIMARY KEY,
name varchar(128)
);
select * from tblsector;id |  name
----+--------- 4 | Aap 1 | Cool 5 | Eerder 2 | Gaafjes 6 | Later 3 | Netjes11 | ICT

I hope that's enough information?

> -----Oorspronkelijk bericht-----
> Van: Peter Eisentraut [mailto:peter_e@gmx.net]
> Verzonden: woensdag 22 juli 2009 20:05
> Aan: pgsql-sql@postgresql.org
> CC: David Weilers
> Onderwerp: Re: [SQL] Double aggregate problem
>
> On Wednesday 22 July 2009 19:16:21 David Weilers wrote:
> > 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
> >
> > That currently produces the following output:
>
> No one is going to be able to reproduce that without the table
definitions and
> data.
>