Python django.db.models.aggregates.Sum() Examples

The following are 30 code examples for showing how to use django.db.models.aggregates.Sum(). These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example.

You may check out the related API usage on the sidebar.

You may also want to check out all available functions/classes of the module django.db.models.aggregates , or try the search function .

Example 1
Project: bioforum   Author: reBiocoder   File: operations.py    License: MIT License 6 votes vote down vote up
def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass
                else:
                    if isinstance(output_field, bad_fields):
                        raise NotImplementedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        ) 
Example 2
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 6 votes vote down vote up
def spend(self, start, finish):
        """
        @param start: the start date the the data is for.
        @param finish: the finish date you want the data for.
        """
        account_first_synced = DailyAccountMetrics.objects.filter(account=self).aggregate(Min('day'))
        first_synced_date = None
        if 'day__min' in account_first_synced:
            first_synced_date = account_first_synced['day__min']

        if not self.account_last_synced or self.account_last_synced < finish or not first_synced_date or first_synced_date > start:
            raise AdwordsDataInconsistencyError('Google Adwords Account %s does not have correct amount of data to calculate the spend between "%s" and "%s"' % (
                self,
                start,
                finish,
            ))

        cost = self.metrics.filter(day__gte=start, day__lte=finish).aggregate(Sum('cost'))['cost__sum']

        if cost is None:
            return 0
        else:
            return cost 
Example 3
Project: Hands-On-Application-Development-with-PyCharm   Author: PacktPublishing   File: operations.py    License: MIT License 6 votes vote down vote up
def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass
                else:
                    if isinstance(output_field, bad_fields):
                        raise utils.NotSupportedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        ) 
Example 4
Project: python   Author: Yeah-Kun   File: operations.py    License: Apache License 2.0 6 votes vote down vote up
def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                    if isinstance(output_field, bad_fields):
                        raise NotImplementedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        )
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass 
Example 5
Project: openhgsenti   Author: drexly   File: operations.py    License: Apache License 2.0 6 votes vote down vote up
def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                    if isinstance(output_field, bad_fields):
                        raise NotImplementedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        )
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass 
Example 6
Project: python2017   Author: bpgc-cte   File: operations.py    License: MIT License 6 votes vote down vote up
def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                    if isinstance(output_field, bad_fields):
                        raise NotImplementedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        )
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass 
Example 7
Project: djongo   Author: nesdis   File: tests.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
def test_department_salary(self):
        qs = Employee.objects.annotate(department_sum=Window(
            expression=Sum('salary'),
            partition_by=F('department'),
            order_by=[F('hire_date').asc()],
        )).order_by('department', 'department_sum')
        self.assertQuerysetEqual(qs, [
            ('Jones', 'Accounting', 45000, 45000),
            ('Jenson', 'Accounting', 45000, 90000),
            ('Williams', 'Accounting', 37000, 127000),
            ('Adams', 'Accounting', 50000, 177000),
            ('Wilkinson', 'IT', 60000, 60000),
            ('Moore', 'IT', 34000, 94000),
            ('Miller', 'Management', 100000, 100000),
            ('Johnson', 'Management', 80000, 180000),
            ('Smith', 'Marketing', 38000, 38000),
            ('Johnson', 'Marketing', 40000, 78000),
            ('Smith', 'Sales', 55000, 55000),
            ('Brown', 'Sales', 53000, 108000),
        ], lambda entry: (entry.name, entry.department, entry.salary, entry.department_sum)) 
Example 8
Project: djongo   Author: nesdis   File: tests.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
def test_multiple_ordering(self):
        """
        Accumulate the salaries over the departments based on hire_date.
        If two people were hired on the same date in the same department, the
        ordering clause will render a different result for those people.
        """
        qs = Employee.objects.annotate(sum=Window(
            expression=Sum('salary'),
            partition_by='department',
            order_by=[F('hire_date').asc(), F('name').asc()],
        )).order_by('department', 'sum')
        self.assertQuerysetEqual(qs, [
            ('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 45000),
            ('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 90000),
            ('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), 127000),
            ('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), 177000),
            ('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 60000),
            ('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 94000),
            ('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 100000),
            ('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), 180000),
            ('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 38000),
            ('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), 78000),
            ('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 55000),
            ('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 108000),
        ], transform=lambda row: (row.name, row.salary, row.department, row.hire_date, row.sum)) 
Example 9
Project: djongo   Author: nesdis   File: tests.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
def test_range_unbound(self):
        """A query with RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING."""
        qs = Employee.objects.annotate(sum=Window(
            expression=Sum('salary'),
            partition_by='department',
            order_by=[F('hire_date').asc(), F('name').asc()],
            frame=ValueRange(start=None, end=None),
        )).order_by('department', 'hire_date', 'name')
        self.assertIn('RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING', str(qs.query))
        self.assertQuerysetEqual(qs, [
            ('Jones', 'Accounting', 45000, datetime.date(2005, 11, 1), 177000),
            ('Jenson', 'Accounting', 45000, datetime.date(2008, 4, 1), 177000),
            ('Williams', 'Accounting', 37000, datetime.date(2009, 6, 1), 177000),
            ('Adams', 'Accounting', 50000, datetime.date(2013, 7, 1), 177000),
            ('Wilkinson', 'IT', 60000, datetime.date(2011, 3, 1), 94000),
            ('Moore', 'IT', 34000, datetime.date(2013, 8, 1), 94000),
            ('Miller', 'Management', 100000, datetime.date(2005, 6, 1), 180000),
            ('Johnson', 'Management', 80000, datetime.date(2005, 7, 1), 180000),
            ('Smith', 'Marketing', 38000, datetime.date(2009, 10, 1), 78000),
            ('Johnson', 'Marketing', 40000, datetime.date(2012, 3, 1), 78000),
            ('Smith', 'Sales', 55000, datetime.date(2007, 6, 1), 108000),
            ('Brown', 'Sales', 53000, datetime.date(2009, 9, 1), 108000),
        ], transform=lambda row: (row.name, row.department, row.salary, row.hire_date, row.sum)) 
Example 10
Project: djongo   Author: nesdis   File: tests.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
def test_distinct_window_function(self):
        """
        Window functions are not aggregates, and hence a query to filter out
        duplicates may be useful.
        """
        qs = Employee.objects.annotate(
            sum=Window(
                expression=Sum('salary'),
                partition_by=ExtractYear('hire_date'),
                order_by=ExtractYear('hire_date')
            ),
            year=ExtractYear('hire_date'),
        ).values('year', 'sum').distinct('year').order_by('year')
        results = [
            {'year': 2005, 'sum': 225000}, {'year': 2007, 'sum': 55000},
            {'year': 2008, 'sum': 45000}, {'year': 2009, 'sum': 128000},
            {'year': 2011, 'sum': 60000}, {'year': 2012, 'sum': 40000},
            {'year': 2013, 'sum': 84000},
        ]
        for idx, val in zip(range(len(results)), results):
            with self.subTest(result=val):
                self.assertEqual(qs[idx], val) 
Example 11
Project: GTDWeb   Author: lanbing510   File: operations.py    License: GNU General Public License v2.0 5 votes vote down vote up
def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            try:
                output_field = expression.input_field.output_field
                if isinstance(output_field, bad_fields):
                    raise NotImplementedError(
                        'You cannot use Sum, Avg, StdDev and Variance aggregations '
                        'on date/time fields in sqlite3 '
                        'since date/time is saved as text.')
            except FieldError:
                # not every sub-expression has an output_field which is fine to
                # ignore
                pass 
Example 12
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def total_impressions_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Sum('impressions')) 
Example 13
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def daily_impressions_for_period(self, start, finish, order_by='day'):
            return self.within_period(start, finish).order_by(order_by).values('day').annotate(impressions=Sum('impressions')) 
Example 14
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def total_clicks_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Sum('clicks')) 
Example 15
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def daily_clicks_for_period(self, start, finish, order_by='day'):
            return self.within_period(start, finish).order_by(order_by).values('day').annotate(clicks=Sum('clicks')) 
Example 16
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def daily_cost_for_period(self, start, finish, order_by='day'):
            return self.within_period(start, finish).order_by(order_by).values('day').annotate(cost=Sum('cost')) 
Example 17
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def total_conversions_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Sum('conversions')) 
Example 18
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def daily_conversions_for_period(self, start, finish, order_by='day'):
            return self.within_period(start, finish).order_by(order_by).values('day').annotate(conversions=Sum('conversions')) 
Example 19
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def total_clicks_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Sum('clicks')) 
Example 20
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def total_clicks(self):
            return self.aggregate(Sum('clicks')) 
Example 21
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def top_by_conversion_rate(self, start, finish):
            return self.filter(metrics__day__gte=start, metrics__day__lte=finish) \
                       .annotate(conversions=Sum('metrics__conversions'),
                                 conv_rate=Avg('metrics__conv_rate'),
                                 cost_conv=Avg('metrics__cost_conv'),
                                 impressions=Sum('metrics__impressions'),
                                 clicks=Sum('metrics__clicks'),
                                 cost=Sum('metrics__cost'),
                                 ctr=Avg('metrics__ctr'),
                                 avg_cpc=Avg('metrics__avg_cpc')) \
                       .order_by('-conversions') 
Example 22
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def total_clicks_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Sum('clicks')) 
Example 23
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def total_clicks(self):
            return self.aggregate(Sum('clicks')) 
Example 24
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def top_by_clicks(self, start, finish):
            return self.filter(metrics__day__gte=start, metrics__day__lte=finish) \
                       .annotate(clicks=Sum('metrics__clicks'),
                                 impressions=Sum('metrics__impressions'),
                                 ctr=Avg('metrics__ctr'),
                                 cost=Sum('metrics__cost'),
                                 avg_position=Avg('metrics__avg_position')) \
                       .order_by('-clicks') 
Example 25
Project: django-google-adwords   Author: alexhayes   File: models.py    License: MIT License 5 votes vote down vote up
def top_by_conversion_rate(self, start, finish):
            return self.filter(metrics__day__gte=start, metrics__day__lte=finish) \
                       .annotate(conversions=Sum('metrics__conversions'),
                                 conv_rate=Avg('metrics__conv_rate'),
                                 cost_conv=Avg('metrics__cost_conv'),
                                 impressions=Sum('metrics__impressions'),
                                 clicks=Sum('metrics__clicks'),
                                 cost=Sum('metrics__cost'),
                                 ctr=Avg('metrics__ctr'),
                                 avg_cpc=Avg('metrics__avg_cpc')) \
                       .order_by('-conversions') 
Example 26
Project: urbanfootprint   Author: CalthorpeAnalytics   File: urban_placetype.py    License: GNU General Public License v3.0 5 votes vote down vote up
def calculate_gross_net_ratio(self):
        all_components = self.get_all_component_percents().all()
        net_components = all_components.filter(placetype_component__component_category__contributes_to_net=True)

        gross = all_components.aggregate(Sum('percent'))['percent__sum']
        net = net_components.aggregate(Sum('percent'))['percent__sum']
        return net / gross

    # So the model is pluralized correctly in the admin. 
Example 27
Project: django-sqlserver   Author: denisenkom   File: test_queryset_values.py    License: MIT License 5 votes vote down vote up
def test_values_expression_group_by(self):
        # values() applies annotate() first, so values selected are grouped by
        # id, not firstname.
        if django.VERSION < (1, 11, 0):
            self.skipTest("does not work on older django")
        Employee.objects.create(firstname='Joe', lastname='Jones', salary=2)
        joes = Employee.objects.filter(firstname='Joe')
        self.assertSequenceEqual(
            joes.values('firstname', sum_salary=Sum('salary')).order_by('sum_salary'),
            [{'firstname': 'Joe', 'sum_salary': 2}, {'firstname': 'Joe', 'sum_salary': 10}],
        )
        self.assertSequenceEqual(
            joes.values('firstname').annotate(sum_salary=Sum('salary')),
            [{'firstname': 'Joe', 'sum_salary': 12}]
        ) 
Example 28
Project: django-sqlserver   Author: denisenkom   File: test_queryset_values.py    License: MIT License 5 votes vote down vote up
def test_chained_values_with_expression(self):
        if django.VERSION < (1, 11, 0):
            self.skipTest("does not work on older django")
        Employee.objects.create(firstname='Joe', lastname='Jones', salary=2)
        joes = Employee.objects.filter(firstname='Joe').values('firstname')
        self.assertSequenceEqual(
            joes.values('firstname', sum_salary=Sum('salary')),
            [{'firstname': 'Joe', 'sum_salary': 12}]
        )
        self.assertSequenceEqual(
            joes.values(sum_salary=Sum('salary')),
            [{'sum_salary': 12}]
        ) 
Example 29
Project: bridge-adaptivity   Author: harvard-vpal   File: base.py    License: BSD 3-Clause "New" or "Revised" License 5 votes vote down vote up
def _get_points_earned_trials_count(self):
        """Get points earned and trials count from the sequence.

        :return tuple([trials_count, points_earned])
        """
        items_result = self.sequence.items.aggregate(
            points_earned=Sum('score'), trials_count=Count('score')
        )
        return items_result['trials_count'], items_result['points_earned'] 
Example 30
Project: bridge-adaptivity   Author: harvard-vpal   File: policy_points_earned.py    License: BSD 3-Clause "New" or "Revised" License 5 votes vote down vote up
def _get_points_earned_trials_count(self):
        """Get points earned and trials count from the sequence.

        :return tuple([trials_count, points_earned])
        """
        # Note(idegtiarov) With the first non-problem activity in the sequence and default value of the threshold
        # item_result returns None, 0 which are not appropriate for the grade calculation method, valid default values
        # are provided to fix this issue.
        items_result = self.sequence.items.exclude(is_problem=False).aggregate(
            points_earned=Coalesce(Sum('score'), 0), trials_count=Greatest(Count('score'), 1)
        )
        return items_result['trials_count'], items_result['points_earned']