CODEWITHSUNDEEP
mysqlsql
neptune
neptune

Reputation: 1261

Grouping columns from a nested select

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.

Upvotes: 0

Views: 41

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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)

Upvotes: 1

Related Questions