I have a result like this:
x
+---------------------------+-----------+------------+| 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 DateI 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 DateI’m very new to SQL, can anyone suggest how can i solve that problem please?
Advertisement
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);