Multi sparse matrices handling with SQL

Question

I got a model like this:

matrices (
    matricesID integer;
    x integer;
    y integer;
    value float;
)

So there will store many matrices data in that table, now I need to get average value for each matrix edge by edge, i.e. if one matrix is 20 * 30 and had value in (5,3), (5,7), (5,15), (12,4), (17,5), (17,10), I need to get four groups of data, one for all values that x=5, one for all values that x=17, one for all values that y=4 and one for all values that y=15, cause they are the max/min for x and y.

Is there any way to perform this with easy SQL?

Any idea will be appreciated.

BR

Edward I

Answer 1

This is a guess as I don't have much experience in the problem domain:

select matricesID
    , (select avg(value) from matrices where matricesID = a.matricesID and x = a.minx) as avgofminx
    , (select avg(value) from matrices where matricesID = a.matricesID and x = a.maxx) as avgofmaxx
    , (select avg(value) from matrices where matricesID = a.matricesID and y = a.miny) as avgofminy
    , (select avg(value) from matrices where matricesID = a.matricesID and y = a.maxy) as avgofmaxy
from (
    select matricesID
        , min(x) as minx
        , max(x) as maxx
        , min(y) as miny
        , max(y) as maxy
    from matrices
    group by matricesID
) as a

This is running in SQL Server, but the syntax is simple enough that it hopefully runs in whatever DBMS you are using