Solved

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

Posted on 2016-09-28
2
57 Views
Last Modified: 2016-09-29
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
Comment
Question by:birwin
2 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 41821301
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
 
LVL 6

Author Closing Comment

by:birwin
ID: 41821384
Brilliant. I tested it on the live server and it seems to be working perfectly.

Thank you so much!!!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question