title: MySQL - Count only unique instances between specific dates
body: <p>I've been looking at several other SO questions but I could not make out a solution from these. First, the description, then what I'm missing from the other threads. (Heads up: I'm very well aware of the non-normalised structure of our database, which is something I have addressed in meetings before but this is what we have and what I have to work with.)</p>
  
  <p><strong>Background description</strong></p>
  
  <p>We have a machine that manufactures products in 25 positions. These products' production data is being logged in a table that among other things logs current and voltage for every position.  This is only logged when the machine is actually producing products (i.e. has a product in the machine). The time where no product is present, nothing is being logged. </p>
  
  <p>This machine can run in two different production modes: full production and R&amp;D production. Full production means that products are being inserted continuously so that every instance has a product at all times (i.e. 25 products are present in the machine at all times). The second mode, R&amp;D production, only produces one product at a time (i.e. one product enters the machine, goes through the 25 instances one by one and when this one is finished, the second product enters the machine).</p>
  
  <p>To clarify: every position logs data once every second whenever a product is present, which means 25 instances per second when full production is running. When R&amp;D mode is running, position 1 will have ~20 instances for 20 consecutive seconds, position 2 will have ~20 instances for the next 20 consecutive seconds and so on.</p>
  
  <p><strong>Table structure</strong></p>
  
  <p>Productiondata:</p>
  
  <ul>
  <li>id (autoincrement)</li>
  <li>productID</li>
  <li>position</li>
  <li>time (timestamp for logged data)</li>
  <li>current (amperes)</li>
  <li>voltage (volts)</li>
  </ul>
  
  <p><strong>Question</strong></p>
  
  <p>We want to calculate the uptime of the machine, but we want to separate the uptime for production mode and R&amp;D mode, and we want to separate this data on a weekly basis.</p>
  
  <p><strong>Guessed solution</strong></p>
  
  <p>Since we have instances logged every second I can count the amount of DISTINCT instances of time values we have in the table to find out the total uptime for both production and R&amp;D mode. To find the R&amp;D mode, I can safely say that whenever there is a time instance that has only one entry, I'm running in R&amp;D mode (production mode would have 25 instances).</p>
  
  <p><strong>Progress so far</strong></p>
  
  <p>I have the following query which sums up all distinct instances to find both production and R&amp;D mode:</p>
  
  <pre><code>SELECT YEARWEEK(time) AS YWeek, COUNT(DISTINCT time) AS Time_Seconds, ROUND(COUNT(DISTINCT time)/3600, 1) AS Time_Hours 
  FROM Database.productiondata
  WHERE YEARWEEK(time) &gt;= YEARWEEK(curdate()) - 21
  GROUP BY YWeek;
  </code></pre>
  
  <p>This query finds out how many DISTINCT time instances there are in the table and counts the number and groups that by the week.</p>
  
  <p><strong>Problem</strong></p>
  
  <p>The above query counts the amount of instances that exist in the table, but I want to find ONLY the UNIQUE instances. Basically, I'm trying to find something like IF count(time) = 1, then count that instance, IF count(time) > 1 then don't count it at all (DISTINCT still counts this).</p>
  
  <p>I looked at several other SO threads, but almost all explain how to find unique values with DISTINCT, which only accomplishes half of what I'm looking for. The closest I got was <a href="https://stackoverflow.com/questions/4242990/mysql-select-rows-with-more-than-one-occurrence">this</a> which uses a HAVING clause. I'm currently stuck at the following:</p>
  
  <pre><code>SELECT YEARWEEK(time) as YWeek, COUNT(Distinct time) As Time_Seconds, ROUND(COUNT(Distinct time)/3600, 1) As Time_Hours
  FROM 
  (SELECT * FROM Database.productiondata
  WHERE time &gt; '2014-01-01 00:00:00'
  GROUP BY time
  HAVING count(time) = 1) as temptime
  GROUP BY YWeek
  ORDER BY YWeek;
  </code></pre>
  
  <p>The problem here is that we have a GROUP BY time inside the nested select clause which takes forever (~5 million rows only for this year so I can understand that). I mean, syntactically I think that this is correct but it takes forever to exectue. Even EXPLAIN for this times out.</p>
  
  <p>And that is where I am. Is this the correct approach or is there any other way that is smarter/requires less query time/avoids the group by time clause?</p>
  
  <p><strong>EDIT:</strong> As a sample, we have this table (apologies for formatting, don't know how to make a table format here on SO)</p>
  
  <pre><code>id    position    time
  1     1           1
  2     2           1
  3     5           1
  4     19          1
  ...   ...         ...
  25    7           1
  26    3           2
  27    6           2
  ...   ...         ...
  </code></pre>
  
  <p>This table shows how it looks like when there is a production run going on. As you can see, there is no general structure for which position gets the first entry when logging the data in the table; what happens is that the 25 positions gets logged during every second and the data is then added to the table depending on how fast the PLC sends the data for every position. The following table shows how the table looks like when it runs in research mode.</p>
  
  <pre><code>id    position    time
  245   1           1
  246   1           2
  247   1           3
  ...   ...         ...
  269   1           25
  270   2           26
  271   2           27
  ...   ...         ...
  </code></pre>
  
  <p>Since all the data is consolidated into one single table, we want to find out how many instances there are when COUNT(time) is exactly equal to 1, or we could look for every instance when COUNT(time) is strictly larger than 1.</p>
  
  <p><strong>EDIT2:</strong> As a reply to Alan, the suggestion gives me</p>
  
  <pre><code>YWeek    Time_Seconds    Time_Hours
  201352   1               0.0
  201352   1               0.0
  201352   1               0.0
  ...      ...             ...
  201352   1               0.0  (1000 row limit)
  </code></pre>
  
  <p>Whereas my desired output is</p>
  
  <pre><code>Yweek    Time_Seconds    Time_Hours
  201352   2146            35.8
  201401   5789            96.5
  ...      ...             ...
  201419   8924            148.7
  </code></pre>
  
  <p><strong>EDIT3:</strong> I have gathered the tries and the results so far <a href="https://i.imgur.com/fG3D3gu.png" rel="nofollow noreferrer">here</a> with a description in gray above the queries.</p>