Calculate 3rd interquartile

• rash3554

  SSCommitted

  Points: 1984

  More actions

  January 8, 2015 at 2:03 pm

  #313497

  Hi there,

  I have a table with values 10,22,11,15,14,14,13,12,12,11,11,11

  Create table test(testno int not null);

  insert into test values(10);

  insert into test values(22);

  insert into test values(11);

  insert into test values(15);

  insert into test values(14);

  insert into test values(14);

  insert into test values(13);

  insert into test values(12);

  insert into test values(12);

  insert into test values(11);

  insert into test values(17);

  insert into test values(11);

  I need to find 1st interquartile and 3rd interquartile and also Q1-1.5(IQR) and Q3+1.5(IQR) to find outliers. I am very new to statistical functions in sqlserver. Can anybody guide me?

• Dwain Camps

  SSC Guru

  Points: 86978

  More actions

  January 8, 2015 at 5:59 pm

  #1768939

  rash3554 (1/8/2015)

  ---

  Hi there,

  I have a table with values 10,22,11,15,14,14,13,12,12,11,11,11

  Create table test(testno int not null);

  insert into test values(10);

  insert into test values(22);

  insert into test values(11);

  insert into test values(15);

  insert into test values(14);

  insert into test values(14);

  insert into test values(13);

  insert into test values(12);

  insert into test values(12);

  insert into test values(11);

  insert into test values(17);

  insert into test values(11);

  I need to find 1st interquartile and 3rd interquartile and also Q1-1.5(IQR) and Q3+1.5(IQR) to find outliers. I am very new to statistical functions in sqlserver. Can anybody guide me?

  SQL (especially not SQL 2008) doesn't have any sophisticated statistical functions.

  Look into NTILE to split into your quartiles and from those (as partitions) it looks like you'll need to calculate median, on which you can get some guidance in this article: https://www.simple-talk.com/sql/t-sql-programming/calculating-the-median-value-within-a-partitioned-set-using-t-sql/ where I discuss a number of reasonably fast methods to do so.

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St