SQL Question

Not able use the average aggregate in view table

I have created a table VQ1 using following clause.

CREATE VIEW VQ1 as
SELECT productid, productname, TO_CHAR(unitprice,'$9,999.99') AS "unitprice"
FROM products
WHERE unitprice > avg(unitprice)
WITH READ ONLY;


I am getting an error that I can not use the aggregate function
AVG()
to find the average.

So how can I find
AVG()
for a view?

Answer

You can try this mate:

CREATE VIEW VQ1 AS
SELECT
    productid,
    productname,
    TO_CHAR(unitprice, '$9,999.99') 'unitprice'
FROM
    products
GROUP BY
    productid
HAVING
    unitprice > AVG(unitprice);