How to filter and sum the array of integers in PostgreSQL

Question

I have a task that requires to sum ALL topup_val performed by the id_user that had at least one topup_val ever by the amount of €15 exactly. This task needs to be solved in a single SELECT statement, without any window functions or subqueries (nested SELECT). I'm still a beginner in SQL so I found it struggle to finish this task.

I converted the topup_val rows for each id_user to array using array_agg(). However, I cannot filter the array using WHERE clause because WHERE clause is performed before the aggregate function.

Thank you so much!

Table topups

 id_user | topup_val
---------+-----------
    1    |    10
    1    |    15
    1    |    5
    2    |    10
    2    |    10
    3    |    15
    3    |    15
    3    |    10

Converted to array

 id_user | topup_array
---------+------------------
    1    |     {10, 15, 5}
    2    |        {10, 10}
    3    |    {15, 15, 10}

Expected result

 id_user | topup_sum
---------+------------
    1    |     30
    3    |     40

My PostgreSQL queries

SELECT id_user, array_agg(topup_val) AS array_topup
    FROM topups 
    WHERE 15 = ANY(array_topup)
    GROUP BY id_user
    ORDER BY id_user;

You can filter on aggregates using the HAVING clause. Add one like HAVING 15 = array_agg(topup_val) (aliases can't be referenced in the HAVING clause). You can also simply use SUM(topup_val) in the SELECT. — Marth
– Marth, Commented Jun 28, 2021 at 15:29

Laurenz Albe · Accepted Answer · 2021-06-28 15:29:28Z

2

Use HAVING rather than WHERE. I appears after the GROUP BY clause and is calculated after aggregation, so it can be used to filter rows after aggregation.

answered Jun 28, 2021 at 15:29

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

Jim Jones · Accepted Answer · 2021-06-28 15:37:35Z

1

Between group by and order by you can further filter your result set with HAVING:

SELECT id_user,sum(topup_val)
FROM topups 
GROUP BY id_user
HAVING array_agg(topup_val) && array[15]
ORDER BY id_user;

Demo: db<>fiddle

WITH topups (id_user,topup_val) AS ( VALUES
(1,10),(1,15),(1,5),(2,10),(2,10),(3,15),(3,15),(3,10)) 
SELECT id_user, sum(topup_val)
FROM topups 
GROUP BY id_user
HAVING array_agg(topup_val) && array[15]
ORDER BY id_user;

 id_user | sum 
---------+-----
       1 |  30
       3 |  40
(2 rows)

answered Jun 28, 2021 at 15:37

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

1 Comment

dataforgood Over a year ago

That's great! I didn't know that I could use the aggregate functions inside HAVING clause, and also array[15] to search for 15 value in the array. Thank you so much!

Collectives™ on Stack Overflow

How to filter and sum the array of integers in PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related