Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: help me to create view

    I need to create one simple view in Oracle database but to my mind I did syntax mistake and I can’t find it. So I need to select all unique cars by model and color and calculate its count

    CREATE or replace VIEW cars_view as
    SELECT
    (select distinct nameCar, color, modelCar from cars where useYear>5),
    (select distinct COUNT(*) nameCar, color, modelCar from cars where useYear>5) as models_count
    from dual;

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Combine two subqueries into one(i.e. remove "SELECT (" , "), (" , ")" and "from dual"),
    and use GROUP BY instead of distinct.
    Last edited by tonkuma; 02-21-12 at 07:21.

  3. #3
    Join Date
    Feb 2012
    Posts
    2
    tonkuma,
    I am junior in oracle and sql. Can you explain more exactly what do you mean

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    CREATE or replace VIEW cars_view as
    select nameCar,color,modelcar,count(*) cnt
    from cars
    where useYear > 5
    group by nameCar,color,modelcar;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It might be better to change "count(*) cnt" to "count(*) as models_count",
    by seeing original CREATE or replace VIEW statement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •