[SOLVED] Return IDs that are x% higher than the average

Question

[SOLVED] Return IDs that are x% higher than the average

Konrad Walsh

SSC Rookie

Points: 49
More actions
September 6, 2017 at 5:45 am

#339045

Hi
This is all very new to me but despite trying to search, I am not sure what I am even asking to be able to search correctly..
I have a dataset that has a column of values. Lets say:
ID Time_Open (secs)
===============
1 50
2 45
3 53
4 70
5 40
6 800
7 65
8 500
9 63
10 32
I want to be able to highlight the ones that are at least 50% greater the overall average of the Time Open column
In this example it would return 6 & 8
So I get the
avg([Time_Open])
I also get
max([Time_Open])
hope i have explained well enough

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

Welcome to SSC.

Normally when posting T-SQL queries, the best way is to provide DDL, DLM and Expected output in consumable formats (have a look at the link in my signature). I've done the DLL and DLM bits for you, but not the expected.

Speaking of your expected results, I'm not sure what you mean by highlight. I've taken this to mean that you want an extra column that denotes values that are more than 1.5 times the value of the average, and another that tells you if the value is the max value. So, in that case, something like this works:
CREATE TABLE #Time (ID int IDENTITY(1,1), Time_Open int);

INSERT INTO #Time (Time_Open) VALUES (50),(45),(53),(70),(40),(800),(65),(500),(63),(32); GO

SELECT * FROM #Time; GO

SELECT ID, Time_Open, CASE WHEN Time_Open > AVG(Time_Open) OVER () * 1.5 THEN 'Yes' ELSE 'No' END AS Well_Above_Avg, CASE WHEN Time_Open = MAX(Time_Open) OVER () THEN 'Max' END AS Max_Time_Flag FROM #Time;

GO DROP TABLE #Time;

If this isn't what you're aiming for, post an expected output so that we can more easily see what your goal is.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Konrad Walsh SSC Rookie Points: 49 More actions · Answer 2

Firstly,, thank you for not flaming me.. and for the welcome 🙂

Secondly, thats for the solution..
I will work with what you sent me to better understand it and come back with questions if I fail.

Konrad Walsh SSC Rookie Points: 49 More actions · Answer 3

Ok
So I have played with your solution and narrowed it down to this:
SELECT ID, [Open_Time], CASE WHEN [Open_Time] > AVG([Open_Time]) OVER () * 1.5 THEN 'Yes' ELSE 'No' END AS Well_Above_Avg FROM [Mods].[dbo].[Diagnostics] Order By Well_Above_Avg desc

This returns this table as expected:

However, what do I need to do end up with just the ones that are yes or over above my avg

I have tired just removing the else..

I am thinking, I need to put it in a temporary table and then use that info and drop the table after but still struggling with that idea.

Konrad Walsh SSC Rookie Points: 49 More actions · Answer 4

I think typing all of that out.. made me say it 'out loud'..
I now have the solution

SELECT CASE WHEN [Open_Time] > AVG([Open_Time]) OVER () * 1.5 THEN ID END AS Well_Above_Avg FROM [Mod].[dbo].[Diagnostics] Order By Well_Above_Avg desc

THANK YO USO MUCH FOR YOUR HELP

Thom A SSC Guru Points: 99143 More actions · Answer 5

A CTE would be a better option:
WITH AboveAvg AS ( SELECT ID, [Open_Time], CASE WHEN [Open_Time] > AVG([Open_Time]) OVER () * 1.5 THEN 'Yes' ELSE 'No' END AS Well_Above_Avg FROM [Mods].[dbo].[Diagnostics]) SELECT * FROM AboveAvg AA WHERE AA.Well_Above_Avg = 'Yes' ORDER BY AA.ID ASC;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk