function to sum all first value of Results SQL

Question

I have a table with "Number", "Name" and "Result" Column. Result is a 2D text Array and I need to create a Column with the name "Average" that sum all first values of Result Array and divide by 2, can somebody help me Pls, I must use the create function for this. Its look like this:

Table1

Number	Name	Result	Average
01	Kevin	{{2.0,10},{3.0,50}}	2.5
02	Max	{{1.0,10},{4.0,30},{5.0,20}}	5.0

Average = ((2.0+3.0)/2) = 2.5

= ((1.0+4.0+5.0)/2) = 5.0

Its a good practice to post your table schema. Also it is totally unclear what function will do. — Akhilesh Mishra, Feb 09 '21 at 17:18

S-Man · Answer 1 · 2021-02-09T15:48:21.573

First of all: You should always avoid storing arrays in the table (or generate them in a subquery if not extremely necessary). Normalize it, it makes life much easier in nearly every single use case.

Second: You should avoid more-dimensional arrays. The are very hard to handle. See Unnest array by one level

However, in your special case you could do something like this:

demo:db<>fiddle

SELECT
    number,
    name,
    SUM(value) FILTER (WHERE idx % 2 = 1) / 2                   -- 2
FROM mytable,
    unnest(avg_result) WITH ORDINALITY as elements(value, idx)  -- 1
GROUP BY number, name

unnest() expands the array elements into one element per record. But this is not an one-level expand: It expand ALL elements in depth. To keep track of your elements, you could add an index using WITH ORDINALITY.
Because you have nested two-elemented arrays, the unnested data can be used as follows: You want to sum all first of two elements, which is every second (the odd ones) element. Using the FILTER clause in the aggregation helps you to aggregate only exact these elements.

However: If that's was a result of a subquery, you should think about doing the operation BEFORE array aggregation (if this is really necessary). This makes things easier.

@MiniPolygon Did this help? Please don't forget to UPVOTE every answer which helped in any way (pointed out a good idea, showed an approach, ...). This honors the time and effort the repliers invested into your question. If one specific answer solves your problem completely, please ACCEPT it addionally to close the question. — S-Man, Feb 16 '21 at 13:23

Akhilesh Mishra · Answer 2 · 2021-02-09T17:22:56.457

Assumptions:

number column is Primary key.
result column is text or varchar type

Here are the steps for your requirements:

Add the column in your table using following query (you can skip this step if column is already added)

alter table table1 add column average decimal;

Update the calculated value by using below query:

update table1 t1
set average = t2.value_
from 
(
select
number, 
sum(t::decimal)/2 as value_ 
from table1 
cross join lateral unnest((result::text[][])[1:999][1]) as t 
group by 1
) t2 
where t1.number=t2.number

Explanation: Here unnest((result::text[][])[1:999][1]) will return the first value of each child array (considering you can have up to 999 child arrays in your 2D array. You can increase or decrease it as per your requirement)

DEMO

Now you can create your function as per your requirement with above query.

function to sum all first value of Results SQL

2 Answers2