COUNT on a DISTINCT query - Mailing list pgsql-sql
| From | Freddy Villalba Arias |
|---|---|
| Subject | COUNT on a DISTINCT query |
| Date | |
| Msg-id | 92EFB0BEDD24E9419E2CD9A2BD35DAEA0438A7@bmsrv001.madrid.bilbomatica.es Whole thread |
| Responses |
Re: COUNT on a DISTINCT query
|
| List | 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.