Let's imagine this database. Here we have some records about cryptocurrencies. Notice that we have two records of SLP token.
pk | owner | order_date | currency | invested_amount_usd |
1 | 1 | 11/2/2022 | BTC | 5000 |
2 | 1 | 11/2/2022 | ETH | 700 |
3 | 1 | 11/2/2022 | SLP | 50 |
4 | 1 | 11/2/2022 | DOT | 5 |
5 | 1 | 11/2/2022 | SLP | 200 |
I need to totalize the invested_amount_usd by sum all the occurences per currency. For example, my two SLP records must sum $250.
Here Django appears, and its pretty simple. I tried to annotate like this, but look that the query has no sense at all...
>>> print(Order.objects.annotate(test=Sum('invested_amount_usd')).query)
SELECT
"order_order"."id",
"order_order"."owner_id",
"order_order"."order_date",
"order_order"."currency",
"order_order"."invested_amount_usd",
SUM("order_order"."invested_amount_usd")
AS "test" FROM "order_order"
GROUP BY
"order_order"."id",
"order_order"."owner_id",
"order_order"."order_date",
"order_order"."currency",
"order_order"."invested_amount_usd",
Because I tried to GROUP BY every columns of my table, and all records have different data the ORM can't group the records.
>>> print(
Order.objects.annotate(test=Sum('invested_amount_usd'))
.values('test')
)
<QuerySet [
{'currency': 'BTC', 'test': 5000.0},
{'currency': 'ETH', 'test': 700.0},
{'currency': 'SLP', 'test': 50.0},
{'currency': 'DOT', 'test': 5.0},
{'currency': 'SLP', 'test': 200.0}
]>
So, I tried using .values() before annotate.
>>> print(
Order.objects
.values('currency')
.annotate(total=Sum('invested_amount_usd'))
.query
)
SELECT
"order_order"."currency",
SUM("order_order"."invested_amount_usd")
AS "total" FROM "order_order"
GROUP BY
"order_order"."currency"
And this is the result!. I got 250 for SLP token.
>>> print(
Order.objects
.values('currency')
.annotate(total=Sum('invested_amount_usd'))
)
<QuerySet [
{'currency': 'BTC', 'total': 5000.0},
{'currency': 'DOT', 'total': 5.0},
{'currency': 'ETH', 'total': 700.0},
{'currency': 'SLP', 'total': 250.0}
]>