Can't Sum an array of numbers

Queries and Resources
,
1

Hello guys, I hit a blocker
Using query I named "addGroups" , I joined two tables to get something like this

image
image993×232 6.04 KB

Now I want to get the sum of the amount per group (also using query JSON)

SELECT group_section,  
CASE WHEN group_section IN ('Group1','Group2',)
THEN (sum(amount))
ELSE 0
END as debit_value

I realized only Group2 sum, because it has only one value which is returned,
the other Groups (1 and 3) return 0 in the display table
However the query ran successfully and the run result was an array of the values
group_section:"Group1"
debit_value:"-10.00,-5.00,1.00,6.00"

I have tried using a transformer return formatDataAsObject(data) in the addGroups but no rows are returned

2

If I'm understanding correctlly then you could try something like this:

select group_section, sum(amount) as debit_value from {{ addGroups.data }} where group_section in ('Group1','Group2') group by group_section

or you could limit it to debit types too
select group_section, sum(amount) as debit_value from {{ addGroups.data }} where group_section in ('Group1','Group2') AND transaction_type = 'DEBIT' group by group_section

Select the sum of the debit value for those sections and group by the section

3