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;

Since you're trying to compare all of the rows to a single value you should make sure that you're only getting that single value once. The following code had decent performance in one of my larger local databases (table names have been changed to protect the innocent):

WITH CTE_Overall_Avg AS (SELECT AVG(total_amt) AS overall_avg FROM Transactions)
SELECT
    TX.prod_cat,
    AVG(TX.total_amt) AS prod_cat_avg,
    MAX(OA.overall_avg) AS overall_avg
FROM
    Transactions TX
INNER JOIN CTE_Overall_Avg OA ON 1 = 1
GROUP BY
    TX.prod_cat
HAVING
    AVG(TX.total_amt) > OA.overall_avg

Select prod_cat
From transactions as t 
inner join prod_cat_info as p 
on t.prod_subcat_code=p.prod_sub_cat_code and t.prod_cat_code = p.prod_cat_code
group by prod_cat
having avg(total_amt) > (select avg(total_amt) from transactions);

What if Quantity and Amount columns are given separately? In that case we need to follow a different way to calculate the overall average and the Category-wise average. I have used CTE below which makes the code look very simple.

WITH CTE_Avg_Cat_Revenue AS(
SELECT P.prod_cat, SUM(T.total_amt)/SUM(T.Qty) AS Cat_Avg
FROM Transactions T
JOIN prod_cat_info P
ON T.prod_cat_code = P.prod_cat_code AND T.prod_subcat_code = P.prod_sub_cat_code
GROUP BY P.prod_cat),

CTE_Overall_Avg AS(
SELECT SUM(T.total_amt)/SUM(T.Qty) AS Overall_Average
FROM Transactions T
)

SELECT * FROM CTE_Avg_Cat_Revenue, CTE_Overall_Avg WHERE CTE_Avg_Cat_Revenue.Cat_Avg > CTE_Overall_Avg.Overall_Average;