Thread: Problem with select statement for duplicate data

Problem with select statement for duplicate data

From
Machiel Richards
Date:
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

Re: Problem with select statement for duplicate data

From
Jayadevan M
Date:
>                 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."






Re: Problem with select statement for duplicate data

From
Lukasz Brodziak
Date:
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"

Re: Problem with select statement for duplicate data

From
Lew
Date:
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.