title: How to use a group by but still access every number values cleverly
body: <p>I need to "group by" my data to distinguish between tests (each test has a specific id, name and temperature) and to calculate their count, standard deviation, etc. But I also need access every raw data value from each group, for further indexes calculations that I do in a python script.</p>
  
  <p>I have found two solution to this problem, but both seems non-optimal/flawed:</p>
  
  <p>1) Using <strong>listagg</strong> to store every raw value that were grouped into a single string row. It does the work but it is not optimized : I concatenate multiples float values into a giant string that I will immediately de-concatenate and convert back to float. That seem necessary and costly.</p>
  
  <p>2) Removing the group by entirely and do the count and standard deviation though <strong>partitioning</strong>. But that seems even worse to me. I don't know if PLSQL/oracle optimizes this, it could be calculating the same count and standard deviation for every line (I don't know how to check this). The query result also becomes messy: since there is no 'group by' anymore, I have to do add multiple checks in my python file in order to differentiate every test data (specific id, name and temperature). </p>
  
  <p>I think that my first solution can be improved but I don't know how. How can I use a group by but still access every number values <strong>cleverly</strong> ? </p>
  
  <p>A function similar to list_agg but with a collection/array output type instead of a string output type could maybe do the trick (a sort of 'array_agg' compatible with oracle), but I don't know any.</p>
  
  <p>EDIT:
  The sample data is complex and probably restricted to the company viewing, but I can show you my simplified query for my 1) :</p>
  
  <pre><code>SELECT 
      rav.rav_testid as test_id, 
      tte.tte_testname as test_name,
      tsc.tsc_temperature as temperature,
      listagg(rav.rav_value, ' ')WITHIN GROUP (ORDER BY rav.rav_value) as all_specific_test_values,
      COUNT(rav.rav_value) as n, 
      STDDEV(rav.rav_value) as sigma,
  
  FROM
      ...
      (8 inner joins)
  
  
  GROUP BY
      rav.rqv_testid, tte.tte_testname,tsc.tsc_temperature
  
  ORDER BY
      rav.RAV_testid, tte.tte_testname, spd.SPD_SPLITNAMEINTERNAL,tsc.tsc_temperature
  </code></pre>
  
  <p>The result looks like :</p>
  
  <pre><code>test_id | test_name  | temperature | all_specific_test_values | n | sigma
  -------------------------------------------------------------------------
  6001    |VADC_A(...) | -40         | ,8094034194946289 ,8(...)| 58 | 0,54
  6001    |VADC_A(...) |  25         | ,5054857852946545 ,6(...)| 56 | 0,24
  6001    |VADC_A(...) |  150        | ,8625754277452524 ,4(...)| 56 | 0,26
  6002    |VADC_B(...) | -40         | ,9874514651548454 ,5(...)| 57 | 0,44
  </code></pre>