Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You could use this query:</p> <pre><code>SELECT regdate, SEC_TO_TIME(TRUNCATE(TIME_TO_SEC(regtime)/(60*30),0)*(60*30)) reginterval, COUNT(CASE WHEN items='item1' THEN 1 END) item1, COUNT(CASE WHEN items='item2' THEN 1 END) item2, COUNT(CASE WHEN items='item3' THEN 1 END) item3, COUNT(CASE WHEN items='item4' THEN 1 END) item4 FROM Data GROUP BY regdate, reginterval </code></pre> <p>If you need to insert the resulting rows into a new table, just add this line at the beginning:</p> <pre><code>INSERT INTO ItemsPerInterval (regdate, reginterval, item1, item2, item3, item4) SELECT ... </code></pre> <p><strong>Edit</strong></p> <p>If you also want to show all intervals, even if they have no values, I think that the best you can do is to have a table <code>Intervals</code> that contains all intervals that you need:</p> <pre><code>CREATE TABLE Intervals ( reginterval time ); INSERT INTO Intervals VALUES ('09:00:00'), ('09:30:00'), ('10:00:00'), ... </code></pre> <p>This query returns all intervals, combined with all dates present in your table:</p> <pre><code>SELECT Dates.regdate, Intervals.reginterval FROM (SELECT DISTINCT regdate FROM Data) Dates, Intervals </code></pre> <p>And this query returns the rows you need:</p> <pre><code>SELECT di.regdate, di.reginterval, COUNT(CASE WHEN Data.items='item1' THEN 1 END) item1, COUNT(CASE WHEN Data.items='item2' THEN 1 END) item2, COUNT(CASE WHEN Data.items='item3' THEN 1 END) item3, COUNT(CASE WHEN Data.items='item4' THEN 1 END) item4 FROM ( SELECT Dates.regdate, Intervals.reginterval FROM (SELECT DISTINCT regdate FROM Data) Dates, Intervals ) di LEFT JOIN Data ON di.regdate=Data.regdate AND di.reginterval=SEC_TO_TIME(TRUNCATE(TIME_TO_SEC(Data.regtime)/(60*30),0)*(60*30)) GROUP BY regdate, reginterval; </code></pre> <p>Please see fiddle <a href="http://sqlfiddle.com/#!2/fcb1b/1" rel="nofollow">here</a>.</p> <p><strong>How does this work</strong></p> <p>The idea here is to convert <code>regtime</code>, which is a field that contains a time, to the number of seconds since the beginning of the day, using <a href="https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_time-to-sec" rel="nofollow">TIME_TO_SEC</a> function:</p> <pre><code>TIME_TO_SEC(regtime) </code></pre> <p>we then divide this number to 60*30 which is the number of second in 30 minutes, keeping only the integer part:</p> <pre><code>TRUNCATE(number_of_seconds/(60*30), 0) </code></pre> <p>then we multiply the integer part back to 60*30 to obtain the number of seconds, rounded by 60*30 seconds (30 minutes).</p> <p>with <a href="https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_sec-to-time" rel="nofollow">SEC_TO_TIME</a> we convert the number of seconds back to a time field.</p>
    singulars
    1. This table or related slice is empty.
    1. POCount recurring records on data base every 30 minutes interval
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USfthiella
    1. USfthiella
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCount recurring records on data base every 30 minutes interval
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you very much for this answer, but lets assume nothing has been registered for example on the the interval 13:00:00 , i still want this to be visible showing 0 for all the data -- see topic edit please
      singulars
      1. PO
      1. USAhmed ElGamil
    2. CO@AhmedElGamil i updated my answer, it will show all 0 when nothing has been registered on an interval
      singulars
      1. PO
      1. USfthiella
    3. COThank you very much it is working. this saved me a lot of run time that could have been done by other queries.
      singulars
      1. PO
      1. USAhmed ElGamil
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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