Which categories have higher-than-avg revenue?

Programming
,
1

I’m trying to get the prod_cats whose average of total_amt is greater than the overall average of total_amt.

Here is my updated 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
GROUP BY prod_cat
HAVING average > (SELECT AVG(total_amt) FROM Transactions)

I’m trying to get the prod_cats whose average of total_amt is greater than the overall average of total_amt from the Transactions table.

2

Your updated code looks correct and should give you the desired result. The code first joins the Transactions table with prod_cat_info table on the prod_cat_code column, groups the result by prod_cat, and calculates the average total_amt for each prod_cat. The HAVING clause filters the result where the average is greater than the overall average of total_amt from the Transactions table, which is obtained using the subquery (SELECT AVG(total_amt) FROM Transactions).