title: SQL:Count of values in one column in relation to another column
body: <p>I have the following table</p>
  
  <pre><code>id      date      time_stamp  licenseid   storeid    deviceid    value
  1     2015-06-12   17:36:15   lic0001       1         0add      52
  2     2015-06-12   17:36:15   lic0002       1         0add      54
  3     2015-06-12   17:36:15   lic0003       1         0add      53
  4     2015-06-12   17:36:21   lic0001       1         0add      54
  5     2015-06-12   17:36:21   lic0002       1         0add      59
  6     2015-06-12   17:36:21   lic0003       1         0add      62
  7     2015-06-12   17:36:21   lic0004       1         0add      55
  8     2015-06-12   17:36:15   lic0001       1         0bdd      53
  9     2015-06-12   17:36:15   lic0002       1         0bdd      52
  10    2015-06-12   17:36:15   lic0003       1         0bdd      52
  </code></pre>
  
  <p>I need the count of deviceid based on the number of timestamps it is seen in. So the output would be something like: 0add is seen in 2 timestamps hence the count is 2 whereas 0bdd is seen in one time stamp hence 0bdd has count of 1. The number of licenses corresponding to the device per time stamp is not considered for the count.</p>
  
  <pre><code>date        deviceid count
  2015-06-12   0add     2
  2015-06-12   0bdd     1
  </code></pre>
  
  <p>I am trying with this query below but unable to verify if it works as the query has been executing for quite some time now and not showing any result :</p>
  
  <pre><code>select date, deviceid, count(deviceid) from my_table group by deviceid, time_stamp
  </code></pre>
  
  <p>Please note that the number of rows I am running this query on is 2,000,000 </p>
  
  <ol>
  <li>Is the above query right for my output</li>
  <li>If so how can I optimize it to run fast for my table size</li>
  </ol>
  
  <p>EDIT: The column labeled <code>time_stamp</code> is a <code>TIME</code> type.</p>