What are the categories for which average revenue is above the overall average

How do I pass the greater than condition? I’m getting an error as invalid column name ‘average’.

Here is my code:

SELECT P.prod_cat, AVG(total_amt) AS average 
FROM Transactions T JOIN 
     prod_cat_info P 
     ON T.prod_cat_code = P.prod_cat_code
WHERE average > AVG(total_amt)
GROUP BY prod_cat

Answer

Based on the title to your question, I think you want:

SELECT p.prod_cat, AVG(t.total_amt) AS average 
FROM (SELECT t.*, AVG(t.total_amt) OVER () as overall_average
      FROM Transactions T
     ) t JOIN
     prod_cat_info P 
     ON T.prod_cat_code = P.prod_cat_code
GROUP BY p.prod_cat, overall_average
HAVING AVG(t.total_amt) > overall_average;