How to group and order rows with different date values in SQL

I have a result like this:

+---------------------------+-----------+------------+
|           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     |
+---------------------------+-----------+------------+

Query is pretty simple:

Select Date, InvoiceID, Amount from TableName order by Date

I need to group them, get sum of amount and order by date. Notice that date values are not the same. I can’t group results because of this. Important thing is i need to order by Date column. This is the result i want:

+-----------+-------------+
| 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      |
+-----------+-------------+

I’m trying this code but sql gave me an error that i need to use Date column in group by clause.

SELECT InvoiceID, sum(Amount) as Amount from TableName group by InvoiceID order by Date

I’m very new to SQL, can anyone suggest how can i solve that problem please?

Answer

You want aggregation, but you need an aggregation function for the ordering as well:

SELECT InvoiceID, sum(Amount) as Amount 
FROM TableName 
GROUP BY InvoiceID 
ORDER BY MIN(Date);

Advertisement

Answer