优化查询以从一个表的两个不同列和不同行计算总数

Table accounts

Id    price     currency      date 
1      10        USD         2013-01-12  
2      200       GBP         2013-01-13 
3      30        GBP         2013-01-14 
4      85        USD         2013-01-20 
5      80        GBP         2013-01-25 
6     120        GBP         2013-01-30 
7     180        GBP         2013-01-31 
8     100        GBP         2013-02-04 

I want to count it as from one month like this:

我想从这样的一个月算起来:

Total:  USD     95  
Total:  GBP     610

1 个解决方案

#1

You only need to use GROUP BY and an aggregate function SUM() to calculate its total.

您只需要使用GROUP BY和聚合函数SUM()来计算其总数。

SELECT  currency, SUM(price) totalPrice
FROM    tableName
GROUP   BY currency

• SQLFiddle Demo
• SQLFiddle Demo (by month)

SQLFiddle演示(按月)

• SQLFiddle Demo (by month and year)

SQLFiddle演示(按月和年)