mysql - 根据多个变量求平均值

Question

我必须找到在 2012 年至少使用过一次的帐户在 2012 年每个月中每种付款类型的平均支出金额。我使用的表包含交易:account_id、purchase_date、 payment_type 和 amount游戏:account_id、game_id 和 game_date

到目前为止我已经

 SELECT SUM(amount) FROM Transactions
 WHERE DATE (purchase_date) BETWEEN '2012-01-01' AND '2012-12-31'
 AND account_id in (SELECT account_id FROM Games WHERE (Games.game_date) 
 BETWEEN '2012-01-01' AND '2012-12-31') GROUP BY payment_type; 

我不知道如何进一步推进这件事。到目前为止，我的结果仅显示一列，因此我什至不知道哪种金额适用于哪种付款类型。如何在这些旁边显示付款类型，然后根据每月的交易数量对它们进行平均？

编辑

所以我有

 SELECT payment_type, AVG(amount) FROM Transactions
 WHERE DATE (purchase_date) BETWEEN '2012-01-01' AND '2012-12-31'
 AND account_id in (SELECT account_id FROM Games WHERE (Games.game_date) 
 BETWEEN '2012-01-01' AND '2012-12-31') GROUP BY payment_type; 

它显示了 2012 年每种付款方式的平均支出，我现在只需按月对其进行排序。

Answer 1

一旦您获得 payment_type 的总和(使用您作为表查询)

   select avg(tot)  from (
      SELECT payment_type, SUM(amount)  as tot FROM Transactions
       WHERE DATE (purchase_date) BETWEEN '2012-01-01' AND '2012-12-31'
       AND account_id in (SELECT account_id FROM Games WHERE (Games.game_date) 
       BETWEEN '2012-01-01' AND '2012-12-31') GROUP BY payment_type
   ) t 

或者可能您只需要

 SELECT payment_type, SUM(amount), AVG(amount)   
 FROM Transactions
 WHERE DATE (purchase_date) BETWEEN '2012-01-01' AND '2012-12-31'
 AND account_id in (SELECT account_id FROM Games WHERE (Games.game_date) 
      BETWEEN '2012-01-01' AND '2012-12-31') 
 GROUP BY payment_type