Skip site navigation (1) Skip section navigation (2)

COUNT on a DISTINCT query

From: "Freddy Villalba Arias" <fvillalba(at)madrid(dot)bilbomatica(dot)es>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: COUNT on a DISTINCT query
Date: 2004-05-05 14:57:23
Message-ID: 92EFB0BEDD24E9419E2CD9A2BD35DAEA0438A7@bmsrv001.madrid.bilbomatica.es (view raw or flat)
Thread:
Lists: 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.

Responses

pgsql-sql by date

Next:From: Theodore PetroskyDate: 2004-05-05 15:10:03
Subject: not really SQL but I need info on BLOBs
Previous:From: Richard HuxtonDate: 2004-05-05 12:21:59
Subject: Test - please ignore

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group