How to filter and sum the array of integers in PostgreSQL

user_11410538 0
Viewed 411

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;
aggregate-functions postgresql arrays sql
2 Answers

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.

user_64643080

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