Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select .. Having vs computer fields

Select .. Having vs computer fields

From
Francisco Reyes
Date:
Can use use computed fields on the "having" part of select?
I have tried:
select a,b, count(*) as dups_count from some_table having dups_count > 1;

and it gives me the error "Attribute 'dups_count' not found"

Re: Select .. Having vs computer fields

From
Javier Dussaillant
Date:
At 12:54 PM 12/12/2001 -0500, Francisco Reyes wrote:
>Can use use computed fields on the "having" part of select?
>I have tried:
>select a,b, count(*) as dups_count from some_table having dups_count > 1;
>
>and it gives me the error "Attribute 'dups_count' not found"

You have to GROUP BY in order to use HAVING. For example:

SELECT a,b, count(*) as dups_count FROM some_table GROUP BY a,b HAVING
dups_count > 1;

You should GROUP your rows by all the columns that are not count() or other
aggregate function (like max(), min(), sum(), etc).




>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly

                 Javier Dussaillant G.
                 ashkar@vrweb.cl
                 Departamento de Desarrollo VRWEB ltda.
                 http://www.vrweb.cl

Re: Select .. Having vs computer fields

From
Francisco Reyes
Date:
On Wed, 12 Dec 2001, Javier Dussaillant wrote:

> At 12:54 PM 12/12/2001 -0500, Francisco Reyes wrote:
> >Can use use computed fields on the "having" part of select?
> >I have tried:
> >select a,b, count(*) as dups_count from some_table having dups_count > 1;
> >
> >and it gives me the error "Attribute 'dups_count' not found"
>
> You have to GROUP BY in order to use HAVING. For example:
>
> SELECT a,b, count(*) as dups_count FROM some_table GROUP BY a,b HAVING
> dups_count > 1;

My mistake for not including it in the example. I did use Group By.

Re: Select .. Having vs computer fields

From
Antonio Fiol Bonnín
Date:
Francisco Reyes wrote:

>On Wed, 12 Dec 2001, Javier Dussaillant wrote:
>
>>At 12:54 PM 12/12/2001 -0500, Francisco Reyes wrote:
>>
>>>Can use use computed fields on the "having" part of select?
>>>I have tried:
>>>select a,b, count(*) as dups_count from some_table having dups_count > 1;
>>>
>>>and it gives me the error "Attribute 'dups_count' not found"
>>>
>>You have to GROUP BY in order to use HAVING. For example:
>>
>>SELECT a,b, count(*) as dups_count FROM some_table GROUP BY a,b HAVING
>>dups_count > 1;
>>
>
>My mistake for not including it in the example. I did use Group By.
>
Rewrite as

SELECT a,b, count(*) as dups_count FROM some_table GROUP BY a,b HAVING
count(*) > 1;


Antonio