title: Number of days in a dataset SQL Oracle without GROUP by
body: <p>Need to calculated the Moving Range for a set of data without using <code>group by</code> clause. As I am calculating the average value and the previous avg value I need to take into account only existing values. I cant not use DIFFDATE(start-end).</p>
  <p>Another constrains is that I need to do it at row-level as I need to have it as a pre-calculated value (denominator) to calculate the AVG Moving Range.</p>
  <p><img src="https://i.stack.imgur.com/dg9Av.png" alt="example" /></p>
  <p>At the moment I am using window functions to calculate the average and previous averages.</p>
  <pre><code>ROUND(AVG(SUMCOUNTSFT3) OVER (partition by to_date(to_char(DATETIMEOFREADING, 'DD/MM/RR'))),2) as AVG_SUMCOUNTSFT3,
              
  ROUND(AVG(SUMCOUNTSFT3) OVER (order by to_date(to_char(DATETIMEOFREADING, 'DD/MM/RR')) RANGE between interval '1' day preceding AND interval '1' day preceding),2) as  LAG_VAL
  </code></pre>
  <p>Here is some sample data, as you can see I have multiple readings from a sensor. I have calculated the average for that day and for the previous day. Then I will have the difference between data points by |Xi - Xi-1|, the denominator is the column that I am trying to calculate. In some cases we will not have reading for a day if the sensor is failing and I need to discard those days if there is no data.</p>
  <p><img src="https://i.stack.imgur.com/YKkcv.png" alt="sample data" /></p>
  <p>I believe a ROW_NUMBER() or DENSE_RANK() will do the job with a partition clause.</p>