Calling doubled records

Databases
1 
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
GROUP BY COUNTRY

The code above produces the result below.

Korea
USA
Japan
Germany
France

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)
GROUP BY COUNTRY

Target result is the following

Korea
USA
Japan

I like to get my target result with your help.

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

Thank you, igor_g.
The code works fine.

4

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?

5

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

6

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

7

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.

8

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