From SQL to Django Queryset API

[Mauro Ziliani]

Hi all.

My name's Mauro and I working on a DB (SQLite3 now and Postgres in the future).

I have a table Movimento with the fields

anno integer,impianto integer,dare decimal(10,2),avere decimal(10,2)

BY hands I can run

SELECT anno,impianto, sum(dare), sum(avere) FROM movimento 

GROUP BY anno,impianto

ORDER BY anno,impianto 

With django Api I write

Movimento.objects.values('anno','impianto').annotate(dare=Sum('dare), avere=Sum('avere))

And I get the same beaviour.

Now I need to translate the following SQL script

SELECT anno,impianto, sum(dare), sum(avere), sum(dare-avere)

FROM movimento 

GROUP BY anno,impianto

ORDER BY anno,impianto 

into django API queryset.

Is it possible todo this in one row?

Cna you give me some idea to solve this translaion?

Finally I'll render the results into a table.

Best regards,

   MZ

[Simon Charette]

Hello Mauro,

The following should do

from django.db.models import F

Movimento.objects.values(

    'anno','impianto'

).annotate(

    dare=Sum('dare),

    avere=Sum('avere),

    diff=Sum(F('dare') - F('avere)),

)

Cheers,

Simon