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

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 

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 

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 

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 

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)) 

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)) 

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)) 

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) 

def test_fail_insert(self):
        """Window expressions can't be used in an INSERT statement."""
        msg = 'Window expressions are not allowed in this query'
        with self.assertRaisesMessage(FieldError, msg):
            Employee.objects.create(
                name='Jameson', department='Management', hire_date=datetime.date(2007, 7, 1),
                salary=Window(expression=Sum(Value(10000), order_by=F('pk').asc())),
            ) 

def get_votes_to_export(self):
        return self.proposals.values(
            'id', 'title', 'author__username', 'author__email'
        ).annotate(
            Sum('votes__rate')
        ).annotate(Count('votes')) 

def get_schedule(self):
        schedule = Activity.objects.filter(track__event=self)\
            .cached_authors()\
            .annotate(Sum('proposal__votes__rate'))\
            .extra(select=dict(track_isnull='track_id IS NULL'))\
            .order_by('track_isnull', 'track_order',
                      '-proposal__votes__rate__sum')
        return schedule 

def test_values_expression_group_by(self):
        # values() applies annotate() first, so values selected are grouped by
        # id, not firstname.
        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}]
        ) 

def test_chained_values_with_expression(self):
        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}]
        ) 

def setUpTestData(cls):
        cls.p1 = Product.objects.create(name='Product1', qty_target=10)
        Stock.objects.create(product=cls.p1, qty_available=5)
        Stock.objects.create(product=cls.p1, qty_available=6)
        cls.p2 = Product.objects.create(name='Product2', qty_target=10)
        Stock.objects.create(product=cls.p2, qty_available=5)
        Stock.objects.create(product=cls.p2, qty_available=5)
        cls.p3 = Product.objects.create(name='Product3', qty_target=10)
        Stock.objects.create(product=cls.p3, qty_available=5)
        Stock.objects.create(product=cls.p3, qty_available=4)
        cls.queryset = Product.objects.annotate(
            qty_available_sum=Sum('stock__qty_available'),
        ).annotate(qty_needed=F('qty_target') - F('qty_available_sum')) 

def test_row_range_rank(self):
        """
        A query with ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING.
        The resulting sum is the sum of the three next (if they exist) and all
        previous rows according to the ordering clause.
        """
        qs = Employee.objects.annotate(sum=Window(
            expression=Sum('salary'),
            order_by=[F('hire_date').asc(), F('name').desc()],
            frame=RowRange(start=None, end=3),
        )).order_by('sum', 'hire_date')
        self.assertIn('ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING', str(qs.query))
        self.assertQuerysetEqual(qs, [
            ('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 280000),
            ('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), 325000),
            ('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 362000),
            ('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 415000),
            ('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 453000),
            ('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), 513000),
            ('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 553000),
            ('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 603000),
            ('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 637000),
            ('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), 637000),
            ('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), 637000),
            ('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 637000),
        ], transform=lambda row: (row.name, row.salary, row.department, row.hire_date, row.sum)) 

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'] 

def test_invalid_start_value_range(self):
        msg = "start argument must be a negative integer, zero, or None, but got '3'."
        with self.assertRaisesMessage(ValueError, msg):
            list(Employee.objects.annotate(test=Window(
                expression=Sum('salary'),
                order_by=F('hire_date').asc(),
                frame=ValueRange(start=3),
            ))) 

def test_invalid_type_end_value_range(self):
        msg = "end argument must be a positive integer, zero, or None, but got 'a'."
        with self.assertRaisesMessage(ValueError, msg):
            list(Employee.objects.annotate(test=Window(
                expression=Sum('salary'),
                order_by=F('hire_date').asc(),
                frame=ValueRange(end='a'),
            ))) 

def test_invalid_type_start_value_range(self):
        msg = "start argument must be a negative integer, zero, or None, but got 'a'."
        with self.assertRaisesMessage(ValueError, msg):
            list(Employee.objects.annotate(test=Window(
                expression=Sum('salary'),
                frame=ValueRange(start='a'),
            ))) 

def test_invalid_type_end_row_range(self):
        msg = "end argument must be a positive integer, zero, or None, but got 'a'."
        with self.assertRaisesMessage(ValueError, msg):
            list(Employee.objects.annotate(test=Window(
                expression=Sum('salary'),
                frame=RowRange(end='a'),
            ))) 

def test_postgresql_illegal_range_frame_start(self):
        msg = 'PostgreSQL only supports UNBOUNDED together with PRECEDING and FOLLOWING.'
        with self.assertRaisesMessage(NotSupportedError, msg):
            list(Employee.objects.annotate(test=Window(
                expression=Sum('salary'),
                order_by=F('hire_date').asc(),
                frame=ValueRange(start=-1),
            ))) 

def test_postgresql_illegal_range_frame_end(self):
        msg = 'PostgreSQL only supports UNBOUNDED together with PRECEDING and FOLLOWING.'
        with self.assertRaisesMessage(NotSupportedError, msg):
            list(Employee.objects.annotate(test=Window(
                expression=Sum('salary'),
                order_by=F('hire_date').asc(),
                frame=ValueRange(end=1),
            ))) 

def test_window_repr(self):
        self.assertEqual(
            repr(Window(expression=Sum('salary'), partition_by='department')),
            '<Window: Sum(F(salary)) OVER (PARTITION BY F(department))>'
        )
        self.assertEqual(
            repr(Window(expression=Avg('salary'), order_by=F('department').asc())),
            '<Window: Avg(F(salary)) OVER (ORDER BY OrderBy(F(department), descending=False))>'
        ) 

def test_empty_group_by_cols(self):
        window = Window(expression=Sum('pk'))
        self.assertEqual(window.get_group_by_cols(), [])
        self.assertFalse(window.contains_aggregate) 

def test_invalid_order_by(self):
        msg = 'order_by must be either an Expression or a sequence of expressions'
        with self.assertRaisesMessage(ValueError, msg):
            Window(expression=Sum('power'), order_by='-horse') 

def test_values_expression_group_by(self):
        # values() applies annotate() first, so values selected are grouped by
        # id, not firstname.
        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}]
        ) 

def test_chained_values_with_expression(self):
        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}]
        ) 

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') 

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.'
                        ) 

def total_impressions_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Sum('impressions'))