“ORA-00998: must name this expression with a column alias” i am having this error can anyone tell me what’s the problem and its solution

this is my sample code;-

CREATE TABLE orders
(

ord_no int,
purch_amt float,
ord_date varchar(50),
customer_id int,
salesman_id int,
PRIMARY KEY(ord_no)
);

INSERT INTO orders (ord_no, purch_amt, ord_date, customer_id, salesman_id)
VALUES (70001, 150.5, '2012-10-05', 3005, 5002);

CREATE VIEW totalforday 
 AS SELECT ord_date , COUNT(DISTINCT customer_id),
 AVG(purch_amt), SUM(purch_amt)
 FROM orders
 GROUP BY ord_date;

Answer

Imagine writing a query for your new view.

SELECT ord_date,
       COUNT(DISTINCT customer_id),
       AVG(purch_amt),
       SUM(purch_amt)
 FROM totalforday;

Your column names – are you asking for a column called SUM(purch_amt), or are you trying to do a sum on a column called purch_amt?

You need to provide a valid column name for the view, either implicitly or explicitly. Adding an alias allows the database to implicitly figure out what to name your column.

Like so.

CREATE or replace VIEW totalforday 
 AS SELECT ord_date , COUNT(DISTINCT customer_id) how_many_customers,
 AVG(purch_amt) avg_amt, SUM(purch_amt) total_amt
 FROM orders
 GROUP BY ord_date;