pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDouble aggregate problem
5+ messages / 3 participants
[nested] [flat]
* Double aggregate problem
@ 2009-07-22 16:16 David Weilers <david@lionhead.nl>
0 siblings, 2 replies; 5+ messages in thread
From: David Weilers @ 2009-07-22 16:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double aggregate problem
@ 2009-07-22 17:45 nha <lyondif02@free.fr>
parent: David Weilers <david@lionhead.nl>
1 sibling, 0 replies; 5+ messages in thread
From: nha @ 2009-07-22 17:45 UTC (permalink / raw)
To: David Weilers <david@lionhead.nl>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double aggregate problem
@ 2009-07-22 18:04 Peter Eisentraut <peter_e@gmx.net>
parent: David Weilers <david@lionhead.nl>
1 sibling, 2 replies; 5+ messages in thread
From: Peter Eisentraut @ 2009-07-22 18:04 UTC (permalink / raw)
To: pgsql-sql; +Cc: David Weilers <david@lionhead.nl>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double aggregate problem
@ 2009-07-23 01:23 nha <lyondif02@free.fr>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 5+ messages in thread
From: nha @ 2009-07-23 01:23 UTC (permalink / raw)
To: David Weilers <david@lionhead.nl>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Double aggregate problem
@ 2009-07-23 08:03 David Weilers <david@lionhead.nl>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 5+ messages in thread
From: David Weilers @ 2009-07-23 08:03 UTC (permalink / raw)
To: pgsql-sql
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 text
overig text,
sollicitatieinfo text,
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 | Netjes
11 | 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.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-07-23 08:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-07-22 16:16 Double aggregate problem David Weilers <david@lionhead.nl>
2009-07-22 17:45 ` nha <lyondif02@free.fr>
2009-07-22 18:04 ` Peter Eisentraut <peter_e@gmx.net>
2009-07-23 01:23 ` nha <lyondif02@free.fr>
2009-07-23 08:03 ` David Weilers <david@lionhead.nl>
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