[SQL] COUNT on a DISTINCT query

[Freddy Villalba Arias]

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.