pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCOUNT on a DISTINCT query
2+ messages / 2 participants
[nested] [flat]
* COUNT on a DISTINCT query
@ 2004-05-05 14:57 Freddy Villalba Arias <fvillalba@madrid.bilbomatica.es>
0 siblings, 1 reply; 2+ messages in thread
From: Freddy Villalba Arias @ 2004-05-05 14:57 UTC (permalink / raw)
To: pgsql-sql
Hello everybody,
I'm a newbie to PostgreSQL.
I have the following query:
SELECT
DISTINCT (at.*)
FROM
AGRUPACION_TERRITORIAL at,
LINK_AGRUP_TE_MUNICIPIO link,
MUNICIPIO m,
PROVINCIA p,
CCAA c
WHERE
at.agru_id_agrupacion_t = link.agmu_id_agrupacion_t AND
link.agmu_id_municipio = m.muni_id_municipio AND
c.ccaa_id_ccaa = p.prov_id_ccaa AND
p.prov_id_provincia = m.muni_id_provincia AND
(
(
(to_char(c.ccaa_id_ccaa, 'FM9999999999999999') = '7')
AND
(
(
(to_char(p.prov_id_provincia, 'FM9999999999999999')
= '2') AND
(
(to_char(m.muni_id_municipio, 'FM9999999999999999')
= '') OR
('' = '')
)
) OR
('2' = '')
)
) OR
('7' = '')
) AND
(
(upper(at.agru_ds_agrupacion) LIKE upper('%%')) OR
('' = '')
)
ORDER BY agru_ds_agrupacion
... which already works.
I wanted to implement the equivalent COUNT statement. Tried this:
SELECT
COUNT (DISTINCT (at.*))
FROM
AGRUPACION_TERRITORIAL at,
LINK_AGRUP_TE_MUNICIPIO link,
MUNICIPIO m,
PROVINCIA p,
CCAA c
WHERE
at.agru_id_agrupacion_t =
link.agmu_id_agrupacion_t AND
link.agmu_id_municipio = m.muni_id_municipio AND
c.ccaa_id_ccaa = p.prov_id_ccaa AND
p.prov_id_provincia = m.muni_id_provincia AND
(
(
(to_char(c.ccaa_id_ccaa, 'FM9999999999999999') = '7')
AND
(
(
(to_char(p.prov_id_provincia, 'FM9999999999999999')
= '2') AND
(
(to_char(m.muni_id_municipio, 'FM9999999999999999')
= '') OR
('' = '')
)
) OR
('2' = '')
)
) OR
('7' = '')
) AND
(
(upper(at.agru_ds_agrupacion) LIKE upper('%%')) OR
('' = '')
)
ORDER BY agru_ds_agrupacion
... which I believe would work in other DBMS like Oracle, but won't work
in PostgreSQL.
I even tried it with a nested statement, like this:
SELECT COUNT(xxx.*) FROM (<the query above>) xxx
That didn't work either.
I'd REALLY appreciate some help with this.
Regards,
Freddy.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: COUNT on a DISTINCT query
@ 2004-05-07 14:19 Suller András <suan2@freemail.hu>
parent: Freddy Villalba Arias <fvillalba@madrid.bilbomatica.es>
0 siblings, 0 replies; 2+ messages in thread
From: Suller András @ 2004-05-07 14:19 UTC (permalink / raw)
To: Freddy Villalba Arias <fvillalba@madrid.bilbomatica.es>; +Cc: pgsql-sql
> SELECT COUNT(xxx.*) FROM (<the query above>) xxx
This cause an error for me:
ERROR: cannot pass result of subquery or join "xxx" to a function
Try this instead:
SELECT COUNT(*) FROM (<the query above>) xxx
It worked for me.
Regards,
Suller Andras
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-05-07 14:19 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-05 14:57 COUNT on a DISTINCT query Freddy Villalba Arias <fvillalba@madrid.bilbomatica.es>
2004-05-07 14:19 ` Suller András <suan2@freemail.hu>
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