CREATE TABLE CUSTOMERS (

CUSTOMER VARCHAR2(32),

CITY VARCHAR2(32));



INSERT INTO CUSTOMERS VALUES('ALBERT','NEWYORK');

INSERT INTO CUSTOMERS VALUES('IBRAHIM','DALLAS');

INSERT INTO CUSTOMERS VALUES('SUNNY','WASHINGTON');

INSERT INTO CUSTOMERS VALUES('JOHN','NEWYORK');

INSERT INTO CUSTOMERS VALUES('SMITH','NEWYORK');

INSERT INTO CUSTOMERS VALUES('RAJATH','NEWYORK');

INSERT INTO CUSTOMERS VALUES('RAMANA','SANJOSE');

COMMIT;

SQL> with

  2    asked as ( select upper('&city') asked from dual),

  3    data as (

  4      select city, count(*) total, 100*ratio_to_report(count(*)) over() pct

  5      from customers

  6      group by city

  7    )

  8  select asked city, nvl(total,0) total, round(nvl(pct,0),2) "%"

  9  from asked left outer join data on city = asked

 10  /

Enter value for city: NEWYORK

CITY         TOTAL          %

------- ---------- ----------

NEWYORK          4      57.14



1 row selected.



SQL> /

Enter value for city: CHICAGO

CITY         TOTAL          %

------- ---------- ----------

CHICAGO          0          0