Thread: Beginner needs help

Hi,

I have a table which has a column of surnames (string) and I would like 
to know how can I retrieve (SELECT) all the repeated surnames, i.e. 
more than one person who has the same surname.

Thanks.

Aaron Chu <astrate@mac.com> writes:

> I have a table which has a column of surnames (string) and I would like to know
> how can I retrieve (SELECT) all the repeated surnames, i.e. more than one
> person who has the same surname.

SELECT surname FROM table GROUP BY surname HAVING count(*) > 1

More generally, read up on GROUP BY.

-- 
greg

* Aaron Chu <astrate@mac.com> [01.03.2003 03:22]:
> Hi,
>
> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.
>
> Thanks.

Say you have such a table:
create table person (   name    varchar(10) not null,   surname varchar(20) not null
);
Now your select:
selectsurname
fromperson
group by surname
having count(surname) > 1;

--

Victor Yegorov

So, if I understand.  You want a list of people whose surnames appear more
than once?

Something like

select count(*), surname
from mytable
group by surname
having count(*) > 1

This will show a unique list of all the surnames that appear more than 1
time.

HTH
Chad
----- Original Message -----
From: "Aaron Chu" <astrate@mac.com>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, February 27, 2003 4:17 PM
Subject: [SQL] Beginner needs help


> Hi,
>
> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.
>
> Thanks.
>
>
> ---------------------------(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
>

On Thu, Feb 27, 2003 at 23:17:06 +0000, Aaron Chu <astrate@mac.com> wrote:
> Hi,
> 
> I have a table which has a column of surnames (string) and I would like 
> to know how can I retrieve (SELECT) all the repeated surnames, i.e. 
> more than one person who has the same surname.

select surname from whatevertable group by surname having count(*) > 1 order by surname;

Aaron Chu wrote:>Hi,>>I have a table which has a column of surnames (string) and I would 
like >to know how can I retrieve (SELECT) all the repeated surnames, 
i.e. >more than one person who has the same surname.
>Thanks.

What exactly you want to do? To eliminate duplicates? Use then:
select surname from users group by surname;

Maybe you want to know which surnames are duplicated? Use then:
select surname from users group by surname having count(*)>1

Regards,
Tomasz Myrta

Aaron,

Thanks for an easy one:

> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.

To just get a list of repeated surnames:

SELECT surname, count(*) as names_count
FROM names_table
GROUP BY surname
HAVING count(*) > 1

To get the records these are attached to, call the above as a subquery:

SELECT names_table.*
FROM names_table
WHERE EXISTS ( SELECT surname, count(*)FROM names_tableGROUP BY surnameHAVING count(*) > 1     AND surname =
names_table.surname); 

--
Josh Berkus
Aglio Database Solutions
San Francisco

In article <b4dtl8$1ejl$1@news.hub.org>, Björn Lundin wrote:
> Aaron Chu wrote:
> 
>> Hi,
>> 
>> I have a table which has a column of surnames (string) and I would like
>> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
>> more than one person who has the same surname.
> 
> select surname, count('a') from table
> group by surname
> having count('a') > 1
> order by surname
> 


SELECT DISTINCT surname FROM table t1, table t2 WHERE t1.surname = t2.surname   AND t1.oid != t2.oid
ORDER BY surname;

Aaron Chu wrote:

> Hi,
> 
> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.

select surname, count('a') from table
group by surname
having count('a') > 1
order by surname

/Björn