Grouping columns from a nested select

43
November 20, 2018, at 08:30 AM

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.

Answer 1

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)
READ ALSO
WP term_exists and wp_insert_term issue

WP term_exists and wp_insert_term issue

Maybe I'm trying to reinvent the wheel, but my issue is that I need to check do custom taxonomy term exists, if not than insert this term and afterwards grab term_taxonomy_id of inserted term and assign it to variableThis variable is later used to assign...

38
How to send json array value to php and save to database using javascript

How to send json array value to php and save to database using javascript

I finally managed to get the json value into an arrayBut now Im struggling how to send the array to my php file to be saved to my database

24
Tensorflow not working right, not sure what is wrong

Tensorflow not working right, not sure what is wrong

I use Android Studio to code and in the tensorflowObjectDetection example, I commented out @Disable and added our Vuforia key, and when I run the program it just crashesI've narrowed it down to this code:

22
Apache Commons Configuration: read from classpath/JAR?

Apache Commons Configuration: read from classpath/JAR?

Does the Apache Commons Configuration library support reading properties/configuration files from the classpath or JAR? I didn't find an API where it can take an InputStream, that returned by getResourceAsStream

18