Re: GROUP BY

@galan:

the __ is reference to a foreign key name for market, and __year is a function extracting the year from the datefield.

Sorry for not posting the model, that would have helped avoiding this.

@matthew

Yes, that's right. That was one error down. The following code works well, except that it doesn't group by week.

    contract_query = ( Project.objects
                              .values('market__market')
                              .filter(department=department_id)
                              .filter(datePipeline__year=year)
                              .filter(stage="WON")
                              .annotate(contracted=Sum('ownProduction'))
                              .annotate(week=ExtractWeek('dateContract'))

So, we can't add week to the first values line, since week has yet to be defined. So I tried moving it to the line after annotation of week:

    contract_query = ( Project.objects
                              .filter(department=department_id)
                              .filter(datePipeline__year=year)
                              .filter(stage="WON")
                              .annotate(contracted=Sum('ownProduction'))
                              .annotate(week=ExtractWeek('dateContract'))
                              .values('market__market','week')

WIth the code above, however, the error comes back: I get a "key error" for "contracted". So is that because of week? I tried:

    contract_query = ( Project.objects
                              .filter(department=department_id)
                              .filter(datePipeline__year=year)
                              .filter(stage="WON")
                              .annotate(contracted=Sum('ownProduction'))
                              .annotate(week=ExtractWeek('dateContract'))
                              .values('market__market')

No, same "key error" for "contracted". It seems to me that having the values() statement after the annotation deletes the annotation- is that correct?

And is that intended behavior?

If so, how can I then group by the week function of a datefield?

Or is there something that I got completely wrong?

thanks + cheers, Mikkel

tirsdag den 14. august 2018 kl. 15.28.01 UTC+2 skrev Matthew Pava:

Ah, the error has the answer.  contract_row is a dictionary, so you need to access it as if it were a dictionary.

contract_sum = contract_row['contracted']

 

From: django...@googlegroups.com [mailto:django...@googlegroups.com] On Behalf Of Mikkel Kromann
Sent: Monday, August 13, 2018 11:51 PM
To: Django users
Subject: Re: GROUP BY

 

 

Thanks. But strange - exactly same error:

'dict' object has no attribute 'contracted'

 

    contract_query = (Project.objects.all()
                                 .filter(department=department_id)
                                 .filter(datePipeline__year=year)
                                 .filter(stage="WON")
                                 .annotate(week=ExtractWeek('dateContract'))
                                 .annotate(contracted=Sum('ownProduction'))
                                 .values('week','market__name', 'contracted')
                                )
    for contract_row in contract_query:
        contract_sum = contract_row.contracted

 

I am somewhat confused by this ...

 

Mikkel

 

 

mandag den 13. august 2018 kl. 22.24.48 UTC+2 skrev Matthew Pava:

You need to include "ownProduction" in your values list prior to the annotation.

 

 

From: django...@googlegroups.com [mailto:django...@googlegroups.com] On Behalf Of Mikkel Kromann
Sent: Monday, August 13, 2018 3:22 PM
To: Django users
Subject: GROUP BY

 

I'm trying to sum the field "ownProduction", grouping it by the foreign key "market" (using the market's human readable name, __name from the Market table) and the week number (which is built-in function __year of the date field "dateContract").

However, when I try to read the results of the query in the last line in the code example below., I get the error:

 

"'dict' object has no attribute 'ownProduction'"

 

So "ownProduction" is to my surprise not a field in the query.

What am I doing wrong?

 

    contract_query = (Project.objects.all()
                             .filter(department=department_id)
                             .filter(datePipeline__year=year)
                             .filter(stage="WON")
                             .annotate(week=ExtractWeek('dateContract'))
                             .values('week','market__name')
                             .annotate(Sum('ownProduction'))
                         )
    for contract_row in contract_query:
        contract_sum = contract_row.ownProduction

 

It doesn't change anything using

                             .annotate(ownProduction = Sum('ownProduction'))

 

 

cheers + thanks, Mikkel

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to djang...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/525b78b7-ada3-4a49-818a-c02fc1bf51b0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to djang...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/d2748446-4d61-44a4-b435-c77eb4957818%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/410dbb7f-5b6f-4347-a637-5eaf4156f388%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.