SQL:Count of values in one column in relation to another column

Question

I have the following table

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

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.

date        deviceid count
2015-06-12   0add     2
2015-06-12   0bdd     1

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 :

select date, deviceid, count(deviceid) from my_table group by deviceid, time_stamp

Please note that the number of rows I am running this query on is 2,000,000

1. Is the above query right for my output
2. If so how can I optimize it to run fast for my table size

EDIT: The column labeled time_stamp is a TIME type.

Answer 1

I think you need to consider a couple of things here:

• If you want the number of timestamps per device for each date, you should be grouping by device and date, not device and timestamp.
• You have rows where a device id has the same date and timestamp, so you may want to consider looking for distinct timestamps in each date.

The fix to the first one is self explanatory, and for the second one you can change your aggregation to COUNT(DISTINCT timestamp). Try this query:

SELECT device_id, date, COUNT(DISTINCT timestamp) AS numRows
FROM myTable
GROUP BY device_id, date;

Here is an SQL Fiddle example using your sample data. It is also worth noting that putting an index on the device_id and date columns may help this query run faster, if this query is still slow for you. See the comments for more discussion on this.