我有这样一个结果:
+---------------------------+-----------+------------+
| Date | InvoiceID | Amount |
+---------------------------+-----------+------------+
| 2020-06-09 12:36:37.433 | AF-1 | 189,876996 |
| 2020-06-09 12:36:37.483 | AF-1 | 59,4 |
| 2020-06-09 12:36:37.490 | AF-1 | 15,8544 |
| 2020-06-09 12:37:42.790 | AF-2 | 20,2 |
| 2020-06-09 12:39:29.453 | AF-4 | 70,6596 |
| 2020-06-09 12:43:30.553 | SF-1 | 47,1064 |
| 2020-06-09 12:43:30.577 | SF-1 | 12,96 |
| 2020-06-09 12:43:30.583 | SF-1 | 17,3664 |
| 2020-06-09 12:44:51.963 | SF-3 | 34,3905 |
| 2020-06-09 12:49:34.147 | TM-1 | 500 |
| 2020-06-09 12:50:26.040 | TM-2 | 150 |
| 2020-06-09 12:50:26.063 | TM-2 | 600 |
| 2020-06-09 12:51:29.817 | GH-1 | 500 |
| 2020-06-09 12:51:29.823 | GH-1 | 313,68 |
+---------------------------+-----------+------------+
查询非常简单:
Select Date, InvoiceID, Amount from TableName order by Date
我需要把他们分组,得到总金额和订单日期。请注意,日期值不同。因为这个我不能把结果分组。重要的是我需要按日期列订购。这就是我想要的结果:
+-----------+-------------+
| InvoiceID | Amount |
+-----------+-------------+
| AF-1 | 265,131396 |
| AF-2 | 20,2 |
| AF-4 | 70,6596 |
| SF-1 | 77,4328 |
| SF-3 | 34,3905 |
| TM-1 | 500 |
| TM-2 | 750 |
| GH-1 | 813,68 |
+-----------+-------------+
我正在尝试此代码,但sql给了我一个错误,我需要在GROUPBY子句中使用date列。
SELECT InvoiceID, sum(Amount) as Amount from TableName group by InvoiceID order by Date
我对sql非常陌生,有人能建议我如何解决这个问题吗?
1条答案
按热度按时间06odsfpq1#
您需要聚合,但也需要用于排序的聚合函数: