Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with columns of Hotel names, city name and country name (of the respective hotels). I need to create a view of unique entries which will contain city name and country name columns. Any suggestions on how to go about it? Eg: There may be 100 hotels with city_name = Miami and country_name = USA. The view should however contain only 1 city_name = Miami and country_name = USA.

I figured I'd need a loop to run through the table but still struggling to put together a query.

Thanks in advance!

share|improve this question
    
You can easily do it with either a group by or distinct but to give you a more specific answer we would need to know which DBMS you are working with. –  Kenneth Fisher Aug 15 '13 at 18:23
    
Thanks for responding Kenneth. I'm using Oracle 11g. –  Karan Bhandarkar Aug 15 '13 at 18:26

1 Answer 1

The simplest solution is this:

SELECT DISTINCT city_name, country_name FROM yourtable

That takes the list of result rows and returns each one only once, even if it appears in the table multiple times. See "DISTINCT" in http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm fro details.

If you also want to know mor information about each row group, like how many hotels exist in each city you can use GROUP BY instead:

SELECT city_name, country_name, COUNT(1) AS Cnt
  FROM yourtable
 GROUP BY city_name, country_name

This again returns each city only once but additionally counts how often it appears in the table. See http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#i2065777 for more details on that.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE hotels(name VARCHAR(100),city_name VARCHAR(100),country_name VARCHAR(100));

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel1','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel2','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel5','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel7','Washington','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel8','New York','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel9','New York','USA');

Query 1:

SELECT DISTINCT city_name,country_name FROM hotels

Results:

|  CITY_NAME | COUNTRY_NAME |
-----------------------------
|    Seattle |          USA |
| Washington |          USA |
|   New York |          USA |

Query 2:

SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 GROUP BY city_name,country_name

Results:

|  CITY_NAME | COUNTRY_NAME | CNT |
-----------------------------------
|    Seattle |          USA |   3 |
| Washington |          USA |   1 |
|   New York |          USA |   2 |

If you want to create a view for each country you can just add a WHERE clause with the country name like this:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE hotels(name VARCHAR(100),city_name VARCHAR(100),country_name VARCHAR(100));

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel1','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel2','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel5','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel7','Hamburg','Germany');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel8','Paris','France');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel9','Paris','France');


CREATE VIEW US_Hotels 
AS
SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 WHERE country_name = 'USA'
 GROUP BY city_name,country_name;


CREATE VIEW Ger_Hotels 
AS
SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 WHERE country_name = 'Germany'
 GROUP BY city_name,country_name;

Query 1:

SELECT * FROM US_Hotels

Results:

| CITY_NAME | COUNTRY_NAME | CNT |
----------------------------------
|   Seattle |          USA |   3 |

Query 2:

SELECT * FROM Ger_Hotels

Results:

| CITY_NAME | COUNTRY_NAME | CNT |
----------------------------------
|   Hamburg |      Germany |   1 |
share|improve this answer
    
Thanks Sebastian, I guess I over thought the problem. –  Karan Bhandarkar Aug 15 '13 at 18:46
    
What if I wanted to create a view for each country? –  Karan Bhandarkar Aug 15 '13 at 20:29
    
Karan, see my update above. –  Sebastian Meine Aug 15 '13 at 21:51
    
Thank you Sebastian. To be honest it did make me understand a lot. –  Karan Bhandarkar Aug 15 '13 at 23:26
    
@KaranBhandarkar, glad I could help. Would you mind marking my response as accepted answer? meta.stackexchange.com/questions/5234/… –  Sebastian Meine Aug 16 '13 at 14:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.