title: Find average revenue by each subcategory of top 5 categories in terms of quantity sold in vba sql
body: <p>I have two data set <code>prod_cat_info</code> and <code>Transactions</code> with two common fields for product category and sub category. I am trying to get average revenue by each subcategory of top 5 category in terms of quantity sold. I tried it from below query in which where clause is not working.</p>
  
  <pre><code>SELECT prod_cat_info.prod_subcat, AVG(Transactions.total_amt)
  FROM Transactions INNER JOIN
      prod_cat_info
      ON Transactions.prod_cat_code = prod_cat_info.prod_cat_code AND
          Transactions.prod_subcat_code = prod_cat_info.prod_sub_cat_code
  GROUP BY prod_cat_info.prod_subcat
  WHERE Transactions.prod_cat_code IN (
      SELECT TOP 5 Transactions.prod_cat_code
      FROM Transactions
      GROUP BY Transactions.prod_cat_code
      ORDER BY SUM(Transactions.Qty) DESC
      )
  GROUP BY prod_cat_info.prod_subcat
  </code></pre>