COUNT on a DISTINCT query

Поиск
Список
Период
Сортировка
От	Freddy Villalba Arias
Тема	COUNT on a DISTINCT query
Дата	5 мая 2004 г. 15:25:23
Msg-id	92EFB0BEDD24E9419E2CD9A2BD35DAEA0438A7@bmsrv001.madrid.bilbomatica.es обсуждение исходный текст
Ответы	Re: COUNT on a DISTINCT query (Suller András <suan2@freemail.hu>)
Список	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>
В списке pgsql-sql по дате отправления:
Предыдущее

От: "Contact AR-SD.NET"
Дата: 05 мая 2004 г., 14:56:12
Сообщение: Re: Check a value in array
Следующее

От: Theodore Petrosky
Дата: 05 мая 2004 г., 16:01:19
Сообщение: not really SQL but I need info on BLOBs
Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

COUNT on a DISTINCT query

Предыдущее

Следующее
