SQL : Get rows with multiple conditions

I have the following table:

id     r_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      52
7     2015-06-12   17:36:21   lic0004       1         0add      55
8     2015-06-12   17:36:15   lic0001       1         0bdd      63
9     2015-06-12   17:36:15   lic0002       1         0bdd      52
10    2015-06-12   17:36:15   lic0003       1         0cdd      52
11    2015-06-12   17:36:15   lic0001       1         0cdd      53
12    2015-06-12   17:36:15   lic0002       1         0cdd      52
13    2015-06-12   17:36:15   lic0004       1         0cdd      52

I need the deviceid for which the device if never goes above a certain value. So in the above table I need say those devices which never had a value above 60. This should return below. Since 0add and 0cdd never go above 60

r_date       deviceid 
2015-06-12   0add 
2015-06-12   0cdd

Now I also need to add another condition to the above to include only those rows which have a count of more than say 1 distinct time_stamp. Since 0cdd has 4 values but all in same time stamp 0cdd will not be included in the table. The final output would be

r_date      device_id   count
2015-06-12   0add        2

I have below query for the second part only but how do I add the first condition to only include value that always are below a certain value?

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

r_date is of type DATE and time of type TIME


Source: mysql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.