Databases
StackOverflow2013
Postsdbo
POSQL grouping datas by 10min step
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL grouping datas by 10min step
    text
    copied!<p>I want to sort average duration times, names and id of processes launched during the last hour, sorted and grouped by steps of 15 minutes</p> <p>Here are my tables :</p> <p><strong>process</strong></p> <ul> <li>id</li> <li>Name</li> <li>Type</li> </ul> <p><strong>processRun</strong></p> <ul> <li>id</li> <li>processid</li> <li>dateStart</li> <li>dateEnd</li> </ul> <p>Here is my sql query :</p> <pre class="lang-sql prettyprint-override"><code>WITH tps AS ( SELECT sysdate-1/24,trunc(TO_DATE((trunc(sysdate,'MI')-MOD(TO_CHAR(sysdate, 'MI'),15)/(24*60))-1/24) + (level) / 96, 'mi') AS DATEREF FROM dual CONNECT BY level &lt;= 96 * (TO_DATE(SYSDATE) - TO_DATE(((trunc(sysdate,'MI')-MOD(TO_CHAR(sysdate, 'MI'),15)/(24*60))-1/24)-1/24)) ), da AS ( SELECT p.id as Id, p.Name as Nom,trunc(pRun.DateStart,'MI') as dateStart, AVG(pRun.DATEEND - pRun.DATESTART)*1440 as VAL FROM process p JOIN processrun pRun ON pRun.processid = p.id WHERE trunc(pRun.DateStart,'MI') BETWEEN trunc(SYSDATE-1/24,'MI') AND trunc(SYSDATE,'MI') AND p.Id = 92 ) SELECT da.Id, da.Nom, da.VAL, da.dateStart, tps.dateref FROM tps LEFT JOIN da ON da.dateStart BETWEEN tps.dateref AND tps.dateref-15/1440 GROUP BY tps.dateref ORDER BY da.dateStart, da.Nom ASC </code></pre> <p>This results in: </p> <pre class="lang-none prettyprint-override"><code>SELECT da.Id, da.Nom, da.VAL, da.dateStart, tps.dateref ERROR at line 13: ORA-00979: not a GROUP BY expression </code></pre> <p>(with the <code>*</code> indicator positioned under <code>da.Id</code>)</p> <p>I know I'm not doing this correctly, but I would like to group the results by a 10min-step, and I don't really know how to fix it with that <code>WITH</code> clause. Normally, I would just use the <code>GROUP BY tps.dateref</code>, but it is not working.</p> <p>Thanks for the help!</p> <hr> <p>Got it! my <code>GROUP BY</code> was false, because of my <code>AVG()</code>. I figured out how to fix it by juste select my <code>AVG()</code> during the last <code>SELECT</code> Here is my now query :</p> <pre><code>WITH tps AS ( SELECT trunc(TO_DATE((trunc(sysdate,'MI')-MOD(TO_CHAR(sysdate, 'MI'),15)/(24*60))-1/24) + (level) / 96, 'mi') AS DATEREF FROM dual CONNECT BY level &lt;= 96 * (TO_DATE(SYSDATE) - TO_DATE(((trunc(sysdate,'MI')-MOD(TO_CHAR(sysdate, 'MI'),15)/(24*60))-1/24)-1/24)) ), da AS ( SELECT p.id as Id, p.Name as Nom,trunc(pRun.DateStart,'MI') as dateStart, pRun.DATEEND - pRun.DATESTART as VAL FROM process p JOIN processrun pRun ON pRun.processid = p.id WHERE trunc(pRun.DateStart,'MI') BETWEEN trunc(SYSDATE-1/24,'MI') AND trunc(SYSDATE,'MI') AND p.Id = 92 ) SELECT da.Id,da.Nom, AVG(da.VAL)*1440,tps.dateref FROM tps LEFT JOIN da ON da.dateStart BETWEEN tps.dateref-15/1440 AND tps.dateref GROUP BY da.Id, da.Nom, tps.dateref ORDER BY tps.dateref, da.Nom ASC </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload