Thread: Problem with select statement for duplicate data

Hi All

        I am trying to runt he following select statement on a table in order search for duplicate rows in the table

                select column1,column2,column3,count() from table group by column1,column2,column3 having count() > 1;

        This however gives me the following error:

                ERROR: count(*) must be used to call a parameterless aggregate function


        Does anybody know what the correct statement or syntax is in postgres in order to run the same type of query?


            I need to go through 974 tables to look for duplicate rows in all the tables, however struggling to get this command right.

Regards
Machiel

>                 select column1,column2,column3,count() from table
> group by column1,column2,column3 having count() > 1;
>
>         This however gives me the following error:
>
>                 ERROR: count(*) must be used to call a parameterless
> aggregate function

Shouldn't it be count(*)?
select column1,column2,column3,count(*).......having count(*) > 1

Regards,
Jayadevan






DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

Hello,

As Jay wrote your query should look like this:
select column1,column2,column3,count(*) from table
 group by column1,column2,column3 having count(*) > 1;

2010/12/21 Jayadevan M <Jayadevan.Maymala@ibsplc.com>:
>>                 select column1,column2,column3,count() from table
>> group by column1,column2,column3 having count() > 1;
>>
>>         This however gives me the following error:
>>
>>                 ERROR: count(*) must be used to call a parameterless
>> aggregate function
>
> Shouldn't it be count(*)?
> select column1,column2,column3,count(*).......having count(*) > 1
>
> Regards,
> Jayadevan
>
>
>
>
>
>
> DISCLAIMER:
>
> "The information in this e-mail and any attachment is intended only for
> the person to whom it is addressed and may contain confidential and/or
> privileged material. If you have received this e-mail in error, kindly
> contact the sender and destroy all copies of the original communication.
> IBS makes no warranty, express or implied, nor guarantees the accuracy,
> adequacy or completeness of the information contained in this email or any
> attachment and is not liable for any errors, defects, omissions, viruses
> or for resultant loss or damage, if any, direct or indirect."
>
>
>
>
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>



--
Łukasz Brodziak
"What if everyting around You isn't quite as it seems,
What if all the world You think You know is an inelaborate dream
When You look at Your reflection is that all you want it to be
What if You could look right through the cracks
Would You find Yourself...... Find Yourself afraid to see"

Machiel Richards wrote:
>> select column1,column2,column3,count() from table
>> group by column1,column2,column3 having count()>  1;
>>
>>  This however gives me the following error:
>>
>> ERROR: count(*) must be used to call a parameterless
>> aggregate function

Jayadevan M wrote:
> Shouldn't it be count(*)?
> select column1,column2,column3,count(*).......having count(*)>  1

That's what the error message says.  It gives the solution in the report of
the problem.

--
Lew
Ceci n'est pas une pipe.