按月份和货币分组为MySQL

Question

我的网上商店里有一张桌子，里面有我所有的销售额。看起来是这样的：

saletime            | saleprice  | fee  | currency
2014-01-01 00:51:27 | 89.00      | 6.9  | USD
2014-01-01 01:20:27 | 249.00     | 9.9  | SEK
2014-01-01 02:31:27 | 849.00     | 19.9 | SEK

我需要得到按月和货币分组的总数。

结果应该是这样的：

Month   | Sum(saleprice) | Sum(fee) | Currency
01-2014 | 9829827.00     | 38738.00 | USD
01-2014 | 443827.00      | 738.00   | SEK
01-2014 | 439827.00      | 4738.00  | NOK

02-2014 | 9829827.00     | 38738.00 | USD
02-2014 | 443827.00      | 738.00   | SEK
02-2014 | 439827.00      | 4738.00  | NOK

03-2014 | 9829827.00     | 38738.00 | USD
03-2014 | 443827.00      | 738.00   | SEK
03-2014 | 439827.00      | 4738.00  | NOK

我不太了解高级分组，我尝试了一些类似于

SELECT SUM(saleprice), SUM(fee), currency 
FROM sales
GROUP BY (month(saletime), year(saletime)), currency

但我失败了。如何解决这一问题？

Answer 1

您需要按月+年进行分组，然后是货币：

SELECT DATE_FORMAT(saletime,"%m-%Y") AS `Month`, SUM(saleprice), SUM(fee), currency
FROM sales
GROUP BY DATE_FORMAT(saletime,"%m-%Y"), currency

如果您想将所有美元货币分组为一组，其余的则分组为另一组：

SELECT DATE_FORMAT(saletime,"%m-%Y") AS `Month`, SUM(saleprice), SUM(fee),
  IF(currency = 'USD', 'USD', 'Other') AS currency
FROM sales
GROUP BY DATE_FORMAT(saletime,"%m-%Y"), currency = 'USD'

Answer 2

是你干的，试着：

SELECT sum(saleprice), sum(fee), currency 
FROM sales
GROUP BY DATE_FORMAT( creation_date, '%Y-%m'), currency