I have a very simple table like that:
CREATE TABLE IF NOT EXISTS LuxLog (
  Sensor TINYINT,
  Lux INT,
  PRIMARY KEY(Sensor)
)
It contains thousands of logs from different sensors.
I would like to have Q1 and Q3 for all sensors.
I can do one query for every data, but it would be better for me to have one query for all sensors (getting Q1 and Q3 back from one query)
I though it would be a fairly simple operation, as quartiles are broadly used and one of the main statistical variables in frequency calculation. The truth is that I found loads of overcomplicated solutions, while I was hoping to find something neat and simple.
Anyone can give me a hint?
Edit: This is a piece of code that I found online, but it is not working for me:
SELECT  SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            GROUP_CONCAT(                 -- 1) make a sorted list of values
                Lux
                ORDER BY Lux
                SEPARATOR ','
            )
        ,   ','                           -- 2) cut at the comma
        ,   75/100 * COUNT(*)        --    at the position beyond the 90% portion
        )
    ,   ','                               -- 3) cut at the comma
    ,   -1                                --    right after the desired list entry
    )                 AS `75th Percentile`
    FROM    LuxLog
    WHERE   Sensor=12
    AND     Lux<>0
I am getting 1 as return value, while it should be a number that can be divided by 10 (10,20,30.....1000)
 
     
     
     
    
 
    