Calling doubled records

country   city
Korea 	  Seoul 	
USA 	  New York
USA 	  L.A. 	
Germany   Berlin 	
Japan 	  Tokyo 	
Fance 	  Paris 	
USA       Chicago
Japan 	  Nagoya 	
Korea 	  Seoul

I have a table “test” like the above.

SELECT country
FROM test

The code above produces the result below.


So far, so good.

Now I like to call some record which comes more than 1 time.
The would be code below is not correct code

SELECT country
FROM test
WHERE country(more than 2 times)

Target result is the following


I like to get my target result with your help.

select country, count(*) as cnt
from test
group by country
having cnt > 1
1 Like

Thank you, igor_g.
The code works fine.

I have another question
I like to call the record which has Korea and Seoul because the record is all same .

The trial code below which I made seems not to work correctly.

SELECT country, city, count(*) as cnt
FROM test
GROUP BY country AND city
having cnt > 1

How can I make it to call the record Korea and Seoul with your help?

You’ve been working on this table for quite some time, it seems…

GROUP BY columns are separated by commas, not “AND”.
GROUP BY country,city

Joon has been wrestling with SQL for at least 10 years, his/her history on this forum is well established

i’m pretty sure it’s not a English/Korean language issue either, but rather, a Joon/SQL issue

i’m pretty sure it’s not a English/Korean language issue either, but rather, a Joon/SQL issue

It was a English/Korean language issue at the beginning.
I admit it is now rather , a joon/SQL issue…
The struggling will be gradually reduced in the future…

Not only lanugage issue, I have been, as r937 pointing out, wrestling with SQL for 10 years but also without computer-science academic background,

I have earned no money for the last 10 years in the on-line field.
Instead of making money in the on-line field, I had to work hard for my living in the off-line field.
That is, I guess, one reason why I do approach the code with inductive way.

As I, dare to say, found I have the code DNA although I have no computer-related academic background.
These days I am approaching the code with deductive way. for gradual reducing the struggling in the future.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.