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 Raw |
| Responses |
Re: COUNT on a DISTINCT query
|
| List | pgsql-sql |
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hello everybody,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial">I’m a newbie to PostgreSQL.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">I have the following query:</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">SELECT </span></font><p class="MsoNormal" style="text-indent:35.4pt"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:10.0pt;font-family:Arial">DISTINCT (at.*)</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial">FROM </span></font><p class="MsoNormal" style="text-indent:35.4pt"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:10.0pt;font-family:Arial">AGRUPACION_TERRITORIAL at,</span></font><p
class="MsoNormal"style="text-indent:35.4pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">LINK_AGRUP_TE_MUNICIPIOlink, </span></font><p class="MsoNormal"
style="text-indent:35.4pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">MUNICIPIO m,
</span></font><pclass="MsoNormal" style="text-indent:35.4pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">PROVINCIAp, </span></font><p class="MsoNormal"
style="text-indent:35.4pt"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">CCAA c
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial">WHERE </span></font><p class="MsoNormal" style="text-indent:35.4pt"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:10.0pt;font-family:Arial">at.agru_id_agrupacion_t =
link.agmu_id_agrupacion_tAND</span></font><p class="MsoNormal" style="text-indent:35.4pt"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial">link.agmu_id_municipio = m.muni_id_municipio
AND</span></font><pclass="MsoNormal" style="text-indent:35.4pt"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:10.0pt;font-family:Arial">c.ccaa_id_ccaa= p.prov_id_ccaa AND </span></font><p class="MsoNormal"
style="text-indent:35.4pt"><fontface="Arial" size="2"><span lang="EN-GB"
style="font-size:10.0pt;font-family:Arial">p.prov_id_provincia= m.muni_id_provincia AND </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> (to_char(c.ccaa_id_ccaa, 'FM9999999999999999') = '7') AND </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> (to_char(p.prov_id_provincia, 'FM9999999999999999') = '2') AND
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> (to_char(m.muni_id_municipio, 'FM9999999999999999') = '') OR
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ('' = '') </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ) OR </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ('2' = '') </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ) OR </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ('7' = '') </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ) AND </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> (upper(at.agru_ds_agrupacion) LIKE upper('%%')) OR </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ('' = '') </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial">ORDER BY agru_ds_agrupacion</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">… which already works.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">I wanted to implement the equivalent COUNT statement. Tried this:</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial"> SELECT </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> COUNT (DISTINCT (at.*))</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> FROM </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> AGRUPACION_TERRITORIAL at,</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> </span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">LINK_AGRUP_TE_MUNICIPIOlink, </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> MUNICIPIO m, </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> PROVINCIA p, </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> CCAA c </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:10.0pt;font-family:Arial">WHERE</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> at.agru_id_agrupacion_t = link.agmu_id_agrupacion_t
AND</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> </span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">link.agmu_id_municipio= m.muni_id_municipio AND</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:10.0pt;font-family:Arial">c.ccaa_id_ccaa= p.prov_id_ccaa AND </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> p.prov_id_provincia = m.muni_id_provincia AND </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> (to_char(c.ccaa_id_ccaa, 'FM9999999999999999') = '7') AND </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> (to_char(p.prov_id_provincia, 'FM9999999999999999') = '2') AND
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> (to_char(m.muni_id_municipio, 'FM9999999999999999') = '') OR
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ('' = '') </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ) OR </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ('2' = '') </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ) OR </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ('7' = '') </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ) AND </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ( </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> (upper(at.agru_ds_agrupacion) LIKE upper('%%')) OR </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ('' = '') </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> ) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size:
10.0pt;font-family:Arial"> ORDER BY agru_ds_agrupacion</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">… which I believe would work in other DBMS like Oracle, but won’t work in
PostgreSQL.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB"
style="font-size:12.0pt">Ieven tried it with a nested statement, like this:</span></font><p class="MsoNormal"><font
face="TimesNew Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt"> </span></font><p class="MsoNormal"><font
face="TimesNew Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt">SELECT COUNT(xxx.*) FROM (<the query
above>)xxx</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB"
style="font-size:12.0pt"> </span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB"
style="font-size:12.0pt">Thatdidn’t work either.</span></font><p class="MsoNormal"><font face="Times New Roman"
size="3"><spanlang="EN-GB" style="font-size:12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman"
size="3"><spanlang="EN-GB" style="font-size:12.0pt">I’d REALLY appreciate some help with this.</span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt"> </span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span lang="EN-GB"
style="font-size:12.0pt">Regards,</span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span
lang="EN-GB"style="font-size:12.0pt">Freddy.</span></font></div>