CODEWITHSUNDEEP
oracle-databasesqlplus
BenniMcBeno
BenniMcBeno

Reputation: 2445

Where two or more values match condition?

I have been asked this question;

You list county names and the surnames of the representatives if the representatives in the counties have the same surname.

and I have the following tables;

***REPRESENTATIVE***
REPI SURNAME    FIRSTNAME  COUNTY     CONS
---- ---------- ---------- ---------- ----
R100 Gorege     Larry      kent       CON1
R101 shneebly   john       kent       CON2
R102 shneebly   steve      kent       CON3

I cant seem to figure out the correct way to ask Orical to display a surname that exists more then twice and the surnames are in the same country.

I know how to ask WHERE something = something, but that's doesn't ask what I want to know.

Upvotes: 0

Views: 57

Answers (1)

Justin Cave
Justin Cave

Reputation: 231721

It sounds like you want to use the HAVING clause after doing a GROUP BY

SELECT surname, county, count(*)
  FROM you_table
 GROUP BY surname, county
HAVING count(*) > 1;

If you really mean "more than twice" as you wrote, none of the data you'd want HAVING count(*) > 2 but then none of your sample data would be returned.

In words, this SQL statement says

  • Group the data into buckets by surname and county. Each distinct combination of surname and county is a separate bucket.
  • Count the number of rows in each bucket
  • Return those buckets where there are at least two rows

Upvotes: 2

Related Questions