Mathematical help and not really SQL

Create table stdevtest
(col1 int)
Insert stdevtest values(1)
Insert stdevtest values(2)
Insert stdevtest values(3)
Insert stdevtest values(6)
Insert stdevtest values(7)
Insert stdevtest values(8)
Insert stdevtest values(8000)
select avg(col1) from stdevtest
select stdev(col1) from stdevtest
As you can see, I tried avg and also standard deviation, but per the values
above and I aint no mathematician, how can I somehow show the business
folks, that there are some outliers and excluding those eg: value 8000, our
average is really around 4 and stdev is around 2.88
Are there other ways to do this ? In this example, atleast I can see all the
data, but when I was analyzing some data with a few 1000 rows, i did a min
and max and found min being 1 and max being like 500000 and hence the avg is
around 200000 or so which may not be what i want to tell the business
folks.. Sad part is I also dont know how to tell them otherwise :(
Appreciate any help you can provide..Hi
You may want skewness see http://en.wikipedia.org/wiki/Skewness
and http://www.users.drew.edu/skass/sql/SkewKurtosis.sql.txt
Also read up on Median
http://en.wikipedia.org/wiki/Median
John
"Hassan" wrote:
> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the values
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, our
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all the
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise :(
> Appreciate any help you can provide..
>
>|||Or you could try something like this:
-- Chop off top and bottom
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
) b
WHERE a.col1 > b.min_col1
AND a.col1 < b.max_col1
-- Chop off top and bottom with tolerance
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
WHERE col1 > 1
AND col1 < 7000
) b
WHERE a.col1 >= b.min_col1
AND a.col1 <= b.max_col1
Curtesty of Ken Henderson originally I think!?
Hope that helps.
wBob
"Hassan" wrote:
> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the values
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, our
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all the
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise :(
> Appreciate any help you can provide..
>
>|||On Mon, 3 Dec 2007 22:59:25 -0800, Hassan wrote:
>Create table stdevtest
>(col1 int)
>Insert stdevtest values(1)
>Insert stdevtest values(2)
>Insert stdevtest values(3)
>Insert stdevtest values(6)
>Insert stdevtest values(7)
>Insert stdevtest values(8)
>Insert stdevtest values(8000)
>select avg(col1) from stdevtest
>select stdev(col1) from stdevtest
>As you can see, I tried avg and also standard deviation, but per the values
>above and I aint no mathematician, how can I somehow show the business
>folks, that there are some outliers and excluding those eg: value 8000, our
>average is really around 4 and stdev is around 2.88
>Are there other ways to do this ? In this example, atleast I can see all the
>data, but when I was analyzing some data with a few 1000 rows, i did a min
>and max and found min being 1 and max being like 500000 and hence the avg is
>around 200000 or so which may not be what i want to tell the business
>folks.. Sad part is I also dont know how to tell them otherwise :(
>Appreciate any help you can provide..
>
Hi Hassan,
If you just want to exclude the highest and lowest values, you can use
SELECT (1.0 * SUM(col1) - MAX(col1) - MIN(col1)) / (COUNT(*) - 2)
FROM stdevtest;
If you want to exclude the 10% highest and 10% lowest values, then use
something like this:
WITH RankedData
AS (SELECT col1 * 1.0 AS col1,
ROW_NUMBER () OVER (ORDER BY col1) AS rn,
COUNT(*) OVER () AS cnt
FROM stdevtest)
SELECT AVG(col1), STDEV(col1)
FROM RankedData
WHERE rn BETWEEN CEILING(cnt * 0.1) + 1 AND FLOOR(cnt * 0.9);
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Mathematical help and not really SQL

Create table stdevtest
(col1 int)
Insert stdevtest values(1)
Insert stdevtest values(2)
Insert stdevtest values(3)
Insert stdevtest values(6)
Insert stdevtest values(7)
Insert stdevtest values(8)
Insert stdevtest values(8000)
select avg(col1) from stdevtest
select stdev(col1) from stdevtest
As you can see, I tried avg and also standard deviation, but per the values
above and I aint no mathematician, how can I somehow show the business
folks, that there are some outliers and excluding those eg: value 8000, our
average is really around 4 and stdev is around 2.88
Are there other ways to do this ? In this example, atleast I can see all the
data, but when I was analyzing some data with a few 1000 rows, i did a min
and max and found min being 1 and max being like 500000 and hence the avg is
around 200000 or so which may not be what i want to tell the business
folks.. Sad part is I also dont know how to tell them otherwise
Appreciate any help you can provide..
Hi
You may want skewness see http://en.wikipedia.org/wiki/Skewness
and http://www.users.drew.edu/skass/sql/SkewKurtosis.sql.txt
Also read up on Median
http://en.wikipedia.org/wiki/Median
John
"Hassan" wrote:

> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the values
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, our
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all the
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise
> Appreciate any help you can provide..
>
>
|||Or you could try something like this:
-- Chop off top and bottom
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
) b
WHERE a.col1 > b.min_col1
AND a.col1 < b.max_col1
-- Chop off top and bottom with tolerance
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
WHERE col1 > 1
AND col1 < 7000
) b
WHERE a.col1 >= b.min_col1
AND a.col1 <= b.max_col1
Curtesty of Ken Henderson originally I think!?
Hope that helps.
wBob
"Hassan" wrote:

> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the values
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, our
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all the
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise
> Appreciate any help you can provide..
>
>

Mathematical help and not really SQL

Create table stdevtest
(col1 int)
Insert stdevtest values(1)
Insert stdevtest values(2)
Insert stdevtest values(3)
Insert stdevtest values(6)
Insert stdevtest values(7)
Insert stdevtest values(8)
Insert stdevtest values(8000)
select avg(col1) from stdevtest
select stdev(col1) from stdevtest
As you can see, I tried avg and also standard deviation, but per the values
above and I aint no mathematician, how can I somehow show the business
folks, that there are some outliers and excluding those eg: value 8000, our
average is really around 4 and stdev is around 2.88
Are there other ways to do this ? In this example, atleast I can see all the
data, but when I was analyzing some data with a few 1000 rows, i did a min
and max and found min being 1 and max being like 500000 and hence the avg is
around 200000 or so which may not be what i want to tell the business
folks.. Sad part is I also dont know how to tell them otherwise
Appreciate any help you can provide..Hi
You may want skewness see http://en.wikipedia.org/wiki/Skewness
and http://www.users.drew.edu/skass/sql...urtosis.sql.txt
Also read up on Median
http://en.wikipedia.org/wiki/Median
John
"Hassan" wrote:

> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the value
s
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, ou
r
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all t
he
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg
is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise
> Appreciate any help you can provide..
>
>|||Or you could try something like this:
-- Chop off top and bottom
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
) b
WHERE a.col1 > b.min_col1
AND a.col1 < b.max_col1
-- Chop off top and bottom with tolerance
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
WHERE col1 > 1
AND col1 < 7000
) b
WHERE a.col1 >= b.min_col1
AND a.col1 <= b.max_col1
Curtesty of Ken Henderson originally I think!?
Hope that helps.
wBob
"Hassan" wrote:

> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the value
s
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, ou
r
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all t
he
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg
is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise
> Appreciate any help you can provide..
>
>|||On Mon, 3 Dec 2007 22:59:25 -0800, Hassan wrote:

>Create table stdevtest
>(col1 int)
>Insert stdevtest values(1)
>Insert stdevtest values(2)
>Insert stdevtest values(3)
>Insert stdevtest values(6)
>Insert stdevtest values(7)
>Insert stdevtest values(8)
>Insert stdevtest values(8000)
>select avg(col1) from stdevtest
>select stdev(col1) from stdevtest
>As you can see, I tried avg and also standard deviation, but per the values
>above and I aint no mathematician, how can I somehow show the business
>folks, that there are some outliers and excluding those eg: value 8000, our
>average is really around 4 and stdev is around 2.88
>Are there other ways to do this ? In this example, atleast I can see all th
e
>data, but when I was analyzing some data with a few 1000 rows, i did a min
>and max and found min being 1 and max being like 500000 and hence the avg i
s
>around 200000 or so which may not be what i want to tell the business
>folks.. Sad part is I also dont know how to tell them otherwise
>Appreciate any help you can provide..
>
Hi Hassan,
If you just want to exclude the highest and lowest values, you can use
SELECT (1.0 * SUM(col1) - MAX(col1) - MIN(col1)) / (COUNT(*) - 2)
FROM stdevtest;
If you want to exclude the 10% highest and 10% lowest values, then use
something like this:
WITH RankedData
AS (SELECT col1 * 1.0 AS col1,
ROW_NUMBER () OVER (ORDER BY col1) AS rn,
COUNT(*) OVER () AS cnt
FROM stdevtest)
SELECT AVG(col1), STDEV(col1)
FROM RankedData
WHERE rn BETWEEN CEILING(cnt * 0.1) + 1 AND FLOOR(cnt * 0.9);
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Master-Details Insert

Master table (tlbProduct) having productID as Primary key and
which acts as reference key for table (tlbCategory).
Fields for tlbProduct are productID,productName
Fields for tlbCategort are productID,CategoryID(primary key),CategoryName,Prize
both productID,CategoryID are autoincrementing.
but when i write two inserts simultaneously as follows
insert into tlbProduct(productName)values(@.productName)
insert into tlbCategory(CategoryName,Prize) using sqldatasource
I get the error that ProductID value is null which is not provided
But (productId in both is autoincremented) and relationship is there in both tables
How to resolve this problem without adding ProductID in second Insert
?
SWati

Hello,

to create a relationship between two tables in sql, you need to match the primary key and the foreign key. So after the first insert, you would need to find out the id that was inserted by the database for the product (you can do this with SELECT SCOPE_IDENTITY()), and then you need to use this id in the second insert statement. If the keys don't match and the database is trying to enforce a relationship between the tables, you will get an error.

Good luck

master..xp_cmdshell Error 997 from GetProxyAccount

Hello all,
I have an aspx page that inserts a record in a table via a stored
procedure. That table has a trigger which gets fired on an insert. The
trigger has the following code
EXEC master..xp_cmdshell 'C:\MyApp\try.exe'
This all works when the stored procedure is fired inserting the record
in turn firing off the trigger; but when the ASPX page runs the stored
procedure I get the following error.
A severe error occurred on the current command. The results, if any,
should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
472
The Guest login has permissions to the extended stored procedure
xp_cmdshell on the master db, but this has not helped.
Any help would be greatly appriciated.
PhilHi,
You need to set up a proxy account.
Create an user and then configure that in EM (Management, right-clickSQL
Agent, configure that proxy account).
Have a look into below article:-
http://support.microsoft.com/defaul...microsoft.com:
80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1
Thanks
Hari
MCDBA
"Phil" <toomuchphill@.hotmail.com> wrote in message
news:8358f7c7.0406132044.6b353856@.posting.google.com...
> Hello all,
> I have an aspx page that inserts a record in a table via a stored
> procedure. That table has a trigger which gets fired on an insert. The
> trigger has the following code
> EXEC master..xp_cmdshell 'C:\MyApp\try.exe'
> This all works when the stored procedure is fired inserting the record
> in turn firing off the trigger; but when the ASPX page runs the stored
> procedure I get the following error.
> A severe error occurred on the current command. The results, if any,
> should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
> 472
> The Guest login has permissions to the extended stored procedure
> xp_cmdshell on the master db, but this has not helped.
> Any help would be greatly appriciated.
> Phil|||Thanks for that Hari,
I am going forward... I think
The error I am getting now is General network error. Check your
network documentation.
I have now configured the proxy account within EM. I unticked the
'Only users with SysAdmin privileges can execute cmdExec and
ActiveScripting job steps.'
I have since changed the trigger from executing my .exe to execute a
.bat which simply copies a file. The Internet Guest account has
privileges to the directory.
The batch file is being run but the copy is not working. I guess this
is to do with the privileges still.
Any suggestions would be greatly appreciated.
Phil.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:<eRBIv2cUEHA.3944@.tk2msftngp13.phx.
gbl>...[vbcol=seagreen]
> Hi,
> You need to set up a proxy account.
> Create an user and then configure that in EM (Management, right-clickSQL
> Agent, configure that proxy account).
> Have a look into below article:-
> [url]http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:[/ur
l]
> 80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1
>
> --
> Thanks
> Hari
> MCDBA
> "Phil" <toomuchphill@.hotmail.com> wrote in message
> news:8358f7c7.0406132044.6b353856@.posting.google.com...|||- Start SQL Enterprise manager
- Open your server
- open the Management folder
- right mouse on SQL Server Agent and select properties
- select Job System tab
- UNCHECK the box in the section 'Non SysAdmin job step proxy account'
which says 'Only users with SysAdmin priveleges can ...'
- when the dialog comes up enter the username, password, [domain] of
some defined Windows user with sysAdmin priveleges (create one if needed fir
st).
- click Apply and OK
"Phil" wrote:

> Hello all,
> I have an aspx page that inserts a record in a table via a stored
> procedure. That table has a trigger which gets fired on an insert. The
> trigger has the following code
> EXEC master..xp_cmdshell 'C:\MyApp\try.exe'
> This all works when the stored procedure is fired inserting the record
> in turn firing off the trigger; but when the ASPX page runs the stored
> procedure I get the following error.
> A severe error occurred on the current command. The results, if any,
> should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
> 472
> The Guest login has permissions to the extended stored procedure
> xp_cmdshell on the master db, but this has not helped.
> Any help would be greatly appriciated.
> Phil
>