troubleshooting Question

If more than 10% of results are over X in mysql

Avatar of birwin
birwinFlag for Canada asked on
MySQL ServerPHP
2 Comments1 Solution101 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros