asked on
If more than 10% of results are over X in mysql
I have a database table with lists of temperature readings from many locations in a number of buildings. I need a query that will give me a TRUE or FALSE if more than 10% of the readings in a building, taken on a date, are greater than X
I am not looking for an average. If there are 100 measurements taken in a building on a date, and 10 of them are over X (say 80 degrees) then show TRUE.
The result I am looking for is a single TRUE or FALSE
If more than 10% of the results for a building/date combination were over X (say 80%) then show TRUE, or some flag equal to true.
The known fields will be building and date. So given the input of building_id (123) and date (2016-04-08) are more than 10% of the entries in the table that have that building number and date greater than X (e.g. 80). The only data to be tested is that for that building and date. So the query would end with "where building_id=`123` AND date=`2016-04-08`"
I am NOT looking for an average or a median. I am NOT looking to see a list of the data that makes up that 10%. I am just looking for TRUE or FALSE.
The table is laid out as
building_id date temperature
| 123 |2016-04-08 | 68.5 |
| 123 |2016-04-08 | 70.2 |
| 123 |2016-04-08 | 65.4 |
| 888 |2013-03 22 | 80.4 |
Typically a building_id / date would have over 100 readings. There are many hundreds of building/date entries in the table.
I am not looking for an average. If there are 100 measurements taken in a building on a date, and 10 of them are over X (say 80 degrees) then show TRUE.
The result I am looking for is a single TRUE or FALSE
If more than 10% of the results for a building/date combination were over X (say 80%) then show TRUE, or some flag equal to true.
The known fields will be building and date. So given the input of building_id (123) and date (2016-04-08) are more than 10% of the entries in the table that have that building number and date greater than X (e.g. 80). The only data to be tested is that for that building and date. So the query would end with "where building_id=`123` AND date=`2016-04-08`"
I am NOT looking for an average or a median. I am NOT looking to see a list of the data that makes up that 10%. I am just looking for TRUE or FALSE.
The table is laid out as
building_id date temperature
| 123 |2016-04-08 | 68.5 |
| 123 |2016-04-08 | 70.2 |
| 123 |2016-04-08 | 65.4 |
| 888 |2013-03 22 | 80.4 |
Typically a building_id / date would have over 100 readings. There are many hundreds of building/date entries in the table.
MySQL ServerPHP
Last Comment
birwin