Solved

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

Posted on 2016-09-28
2
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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 and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

717 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