?
Solved

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

Posted on 2016-09-28
2
Medium Priority
?
67 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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

764 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