Tukukkk

I have a table table1 with the following columns:

id (INT)

label (VARCHAR)

value (INT)

I want to retrieve both the average of the value column, the entire row where the value is the largest, and the row with the lowest value. Something like this:

{

    average: xxx,

    maxval: {

        id: x,

        label: x,

        value: x

    },

    minval: {

        id: x,

        label: x,

        value: x

    }

}

Tried with the following query:

SELECT 

AVG(a.value) AS average, 

b.*,

c.*

FROM table1 a

INNER JOIN table1 b ON ( b.value = (SELECT MAX(value) FROM table1 WHERE label = "el")) 

INNER JOIN table1 c ON ( c.value = (SELECT MIN(value) FROM table1 WHERE label = "el")) 

WHERE a.label = "el";

but this will return all the fields in a single level, like this:

{

    average,

    id,

    label,

    value,

    id,

    label,

    value

}

Tried this too:

SELECT 

AVG(a.value) AS average, 

(SELECT b.* FROM table1 b WHERE ( b.value = (SELECT MAX(value) FROM table1 WHERE label = "el"))) AS maxval,

(SELECT c.* FROM table1 c WHERE ( c.value = (SELECT MIN(value) FROM table1 WHERE label = "el"))) AS minval

FROM table1 a

WHERE a.label = "el";

This last one give an error as I'm trying to use a single alias on multiple columns.

Using MySQL 5.5.

You need do separated querys for each one

SELECT q1.*, q2.*, q3.*

FROM (SELECT AVG(value)

      FROM  Table1) as q1

CROSS JOIN ( SELECT *

             FROM Table1

             ORDER BY value

             LIMIT 1) as q2

CROSS JOIN ( SELECT *

             FROM Table1

             ORDER BY value DESC

             LIMIT 1) as q3

You can use JSON_OBJECT(key1, value1, key2, value2, ... key(n), value(n))

 SELECT q1.*, 

    JSON_OBJECT("id", q2.id, "label", q2.label,"value", q2.value),

    JSON_OBJECT("id", q3.id, "label", q3.label,"value", q3.value)