A query with "GROUP BY" and summation of products of two columns

[Mikkel Kromann]

Dear Django users.

I'm trying to do the sums of the product of two columns ('contract_sum' and 'probability', decimal fields), grouping by a third column, 'stage' (a choice variable).

I'm also reporting the grouped sums of one of the single columns ('contract_sum')

The single column works just fine, but I just can't get it right with the product columns.

Below is my code so far:

contracts = (Project.objects.values('stage')
                            .annotate(contract_sum=Sum('contractSum'))
                            .annotate(expected_own_production_sum=F('contractSum')*F('probability'))
                            )

a) When I run the query with the single column query, the results are right.

b) When I run the query with both selections, not only is the column product, wrong, but the single column sums also become wrong.

c) When I run the query with only the product columns, the query returns zero for all groups, which is definitely wrong.

Can I do this right without going to raw sql?

cheers + thanks, Mikkel

[Mikkel Kromann]

Apologies. The code I posted is actually correct and well functioning.

The reason that the results looked wrong was that the data handled by the code was faulty.

In my research, I found another way to do the trick:

    contracts = (Project.objects.values('stage')
                                .annotate(contract_sum=Sum('contractSum'))

                                .annotate(expected_own_production_sum=Sum('id', field='contractSum * probability'))
                                )

cheers, Mikkel