• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

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.
0
birwin
Asked:
birwin
1 Solution
 
SharathData EngineerCommented:
try this.
select building_id,date_column,
       case when count(case when temprature > 80 then 1 end)*100/count(*) > 10 then 'TRUE' else 'FALSE' end flag
  from myTable 
 group by building_id, date_column order by 1,2;

Open in new window

Here is an example http://sqlfiddle.com/#!9/002098/2
0
 
birwinAuthor Commented:
Brilliant. I tested it on the live server and it seems to be working perfectly.

Thank you so much!!!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now