StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is closely related to, but slightly different from, this question about <a href="https://stackoverflow.com/questions/384142/how-to-get-latest-record-for-each-day-when-there-are-multiple-entries-per-day">How to get the latest record for each day when there are multiple entries per day</a>. (<em>One point in common with many, many SQL questions - the table name was not given originally!</em>)</p> <p>The basic technique will be to find a function that will format the varied Registered_on values such that all the entries in a particular hour are grouped together. This presumably can be done with <code>TO_CHAR()</code> since we're dealing with Oracle (MySQL does not support this).</p> <pre><code>SELECT TO_CHAR(Registered_on, "YYYY-MM-DD HH24") AS TimeSlot, COUNT(*) AS Registrations FROM EmployeeReg GROUP BY 1 ORDER BY 1; </code></pre> <p>You might be able to replace the '1' entries by TimeSlot, or by the <code>TO_CHAR()</code> expression; however, for reasons of backwards compatibility, it is likely that this will work as written (but I cannot verify that for you on Oracle - an equivalent works OK on IBM Informix Dynamic Server using <code>EXTEND(Registered_on, YEAR TO HOUR)</code> in place of <code>TO_CHAR()</code>).</p> <p>If you then decide you want zeroes to appear for hours when there are no entries, then you will need to create a list of all the hours you do want reported, and you will need to do a LEFT OUTER JOIN of that list with the result from this query. The hard part is generating the correct list - different DBMS have different ways of doing it.</p>

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