How to calculate sum of array elements using PostgreSQL

Question

Here is what I have.

class Array(Subquery):
    template = 'ARRAY(%(subquery)s)'

class A(models.Model):
    ...

class B(models.Model):
    a = models.ForeignKey(A)

class C(models.Model):
    b = models.ForeignKey(B)

b_sub = B.objects.filter(a=OuterRef('pk').annotate(items=Count('c').values('items')

result = a.objects.annotate(items=Array(b_sub))

And I'm getting

# >>> result.first().items
[4, 6, 10]

But I need the sum of all items(4+6+10)->20 for each A row.

Like this

# >>> result.first().items
20

Is it possible?

Aleksey · Accepted Answer · 2018-11-26 14:21:02Z

2

Not sure, understand correct or no your question but what about sql query, like this:

SELECT ID, (SELECT SUM(A) FROM UNNEST(MY_COLUMN) AS A) AS TOTAL FROM MY_TABLE;

answered Nov 26, 2018 at 14:21

Aleksey

1,3579 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex · Accepted Answer · 2018-11-26 14:28:12Z

0

Something like this:

from django.db.models import Sum
result = a.objects.annotate(items=Array(b_sub)).aggregate(Sum(items))

answered Nov 26, 2018 at 14:28

Alex

2,50419 silver badges20 bronze badges

1 Comment

Davit Tovmasyan Over a year ago

ProgrammingError: function sum(bigint[]) does not exist LINE 1: SELECT SUM("items") FROM (SELECT "A"."id" AS Col1, ARRA... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Davit Tovmasyan · Accepted Answer · 2018-12-06 15:00:25Z

0

Following is used for solution

class SubqueryCount(Subquery):
    template = '(SELECT count(*) FROM (%(subquery)s) _count)'
    output_field = IntegerField()


sub = C.objects.filter(b__a=OuterRef('pk')).only('pk')
result = a.objects.annotate(count=SubqueryCount(sub))

answered Dec 6, 2018 at 15:00

Davit Tovmasyan

3,6382 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

How to calculate sum of array elements using PostgreSQL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related