max no. of values in WHERE ... IN clause

Hi all,

can anyone tell mey what the maximum number of values (if there is any) which can be used in the WHERE ... IN clause?

ex:
... WHERE ID IN (1, 2, 3 ... , n) ...
what is the maximum number of values which I can put between paranthesis?

10x in advance
mihai.That's decided by the specific DB system.|||10x in advance

"10X" = "thanks"? Whatever next...!|||How many can you put there is one question. How many can you put there and still get an answer while you are young enough to care is a different question. The answer to both questions varies significantly from one query and database engine to another.

-PatP

max elements from table

Hi,
my table has COL1, COl2 with values below:

COL1 COL2
joy 10
joy 11
nik 10
nik 11
nik 12
ale 11
ale 12

How can I select rows with max value for COL2?
that is I want

COL1 COL2
joy 11
nik 12
ale 12

Thanks in advance.
Sunday.SAMPLE Script : run it against some test DB in Query Analyzer .
--=======================================

declare @.tbl table (COL1 int ,COL2 varchar(30) )

insert into @.tbl (COL1,COL2) values (1,'JOY')
insert into @.tbl (COL1,COL2) values (2,'JOY')
insert into @.tbl (COL1,COL2) values (3,'JOY')

insert into @.tbl (COL1,COL2) values (1,'NIK')
insert into @.tbl (COL1,COL2) values (2,'NIK')
insert into @.tbl (COL1,COL2) values (3,'NIK')

insert into @.tbl (COL1,COL2) values (1,'ALE')
insert into @.tbl (COL1,COL2) values (2,'ALE')
insert into @.tbl (COL1,COL2) values (3,'ALE')
insert into @.tbl (COL1,COL2) values (4,'ALE')
insert into @.tbl (COL1,COL2) values (5,'ALE')

insert into @.tbl (COL1,COL2) values (2,'ROY')
insert into @.tbl (COL1,COL2) values (3,'ROY')

insert into @.tbl (COL1,COL2) values (1,'STU')
insert into @.tbl (COL1,COL2) values (2,'STU')
insert into @.tbl (COL1,COL2) values (3,'STU')
insert into @.tbl (COL1,COL2) values (4,'STU')
insert into @.tbl (COL1,COL2) values (5,'STU')

-- pure GROUP BY Clause
SELECT Max(COL1) AS 'COL1',COL2 FROM @.tbl GROUP BY COL2

-- GROUP By Clause with additional ORDER BY
SELECT Max(COL1) AS 'COL1',COL2 FROM @.tbl GROUP BY COL2 ORDER BY COL1 DESC

-- REFER to Books On Line for more information on the TOPIC
--=============================================

regards.
srdjan|||Hi,
What do you think about that one:

I named my table 'tb', put alias 'a' and 'b' on it:

_____________
select a.* from tb a where col2 = (select MAX(col2) from tb b where b.col1 = a.col1)
_____________

MAX COUNT

I have a data set with a list of text values for a survey.
Survey Response Text
54548 More than once a week
84318 More than once a week
81367 Once a week
87186 More than once a week
44987 Twice a week
84938 Twice a week
I've put them into a matrix to display the reponse count.
Response Text Count
More than once a week 3
Twice a week 2
Once a week 1
To do this I used: =COUNT(Fields!ResponseText.Value)
Now what I need to do is make a one-line table/matrix with only the maximum count value of the Response Text that looks something like this:
Response Text Count
More than once a week 3
Unfortunately, I can't do MAX(COUNT(Fields!ResponseText.Value)). MAX(Fields!ResponseText.Value) just gives me the largest string value. Is there a way I can achieve this without creating an additional dataset just to pull the maximum count value in?
Any help would be appreciated.
Thanks,
TedI'm afraid not. Aggregates of aggregates is on our wishlist for a future
version, but for now you'd need to either have a separate data set like you
describe or you could include the counts in your base data set (and do the
max in the report).
However, since you seem to be showing only the aggregates in your matrix
(and none of the details), you could just do this:
select ResponseText, Count(SurveyID) as RespCount from Surveys Group By
ResponseText
And then your grand total would be: =Max(Fields!RespCount.Value)
In fact, you could just put that into the data cell of the matrix (since the
max of one item is that item) and then add a subtotal to the matrix.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Ted" <Ted@.discussions.microsoft.com> wrote in message
news:EF2AC082-1DBE-4DF5-854F-F671DB674D5E@.microsoft.com...
> I have a data set with a list of text values for a survey.
> Survey Response Text
> 54548 More than once a week
> 84318 More than once a week
> 81367 Once a week
> 87186 More than once a week
> 44987 Twice a week
> 84938 Twice a week
> I've put them into a matrix to display the reponse count.
> Response Text Count
> More than once a week 3
> Twice a week 2
> Once a week 1
> To do this I used: =COUNT(Fields!ResponseText.Value)
> Now what I need to do is make a one-line table/matrix with only the
maximum count value of the Response Text that looks something like this:
> Response Text Count
> More than once a week 3
> Unfortunately, I can't do MAX(COUNT(Fields!ResponseText.Value)).
MAX(Fields!ResponseText.Value) just gives me the largest string value. Is
there a way I can achieve this without creating an additional dataset just
to pull the maximum count value in?
> Any help would be appreciated.
> Thanks,
> Ted

Matrix: Hide null value row

The matrix that i have contains null values and is creating empty rows. I tried grouping the row and then setting the visible property, but that just hides the entire rows. Is there an expression that i would need to ensure that only the null rows are not visible on the matrix?

Thanks for taking the time to read.

The database tables looks like:

Month Sales Product

- - --

August 2007 700.00 Apples

August 2007 400.00 Oranges
September 2007 380.00 Apples
October 2007 1200.00 Oranges
November 2007 NULL NULL
December 2007 NULL NULL
Jan 2008 400.00 Grapefruit

The matrix looks like:

August 2007 Sept 2007 Oct 2007 Nov 2007 Dec 2007 Jan 2008

Apples 700.00 380.00

Oranges 400.00 1200.00

Grapfruit 400.00

Try filtering these records. You can use the filter within the Matrix properties to filter all records where Product Names are NULL

|||

I actually tried filtering in the edit group searching for nulls but it didn't work.

I did a bit of searching and found that i needing to use this: =IsNothing(Fields!productname.Value) in the filter expression for the group.

Thanks for your help.sql

Matrix Zero Values

My Matrix presently shows results as follows.
column 1 column 2
row 1 23
row 2 34 34
How would I make the default null value to display as zero. Eg. Row 1, Column 1 would have a value of 0You will need an expression:
=iif(First(Fields!<FieldName>.Value) is Nothing, 0,
First(Fields!<FieldName>.Value))
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:9D9A293F-1E84-439E-B1D5-EF6E76DFD068@.microsoft.com...
> My Matrix presently shows results as follows.
> column 1 column 2
> row 1 23
> row 2 34 34
> How would I make the default null value to display as zero. Eg. Row 1,
Column 1 would have a value of 0

Matrix with fixed column values (months 1-12)

I have a matrix that shows sales per year (rows) and month (columns).
If the data being used contains no records for a particular month for any
year (e.g. November), that column (i.e. column 11) is completely missing
from the matrix.
Can the matrix be configured to always show a given set of columns, even if
there is no underlying data?
ThanksHi Laurence,
If you don't have any data rows for a certain month, then it will not show
up in the matrix grouping. To ensure that certain groups/data values are
always present, you will need an outer join in your dataset query e.g. with
a simple table that just has 12 rows with one column and values from 1 to
12.
Details on how to use outer joins are available here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
HTH,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
> I have a matrix that shows sales per year (rows) and month (columns).
> If the data being used contains no records for a particular month for any
> year (e.g. November), that column (i.e. column 11) is completely missing
> from the matrix.
> Can the matrix be configured to always show a given set of columns, even
if
> there is no underlying data?
> Thanks
>|||OK thanks, I know how to do that.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uWTPFZyuEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi Laurence,
> If you don't have any data rows for a certain month, then it will not show
> up in the matrix grouping. To ensure that certain groups/data values are
> always present, you will need an outer join in your dataset query e.g.
> with
> a simple table that just has 12 rows with one column and values from 1 to
> 12.
> Details on how to use outer joins are available here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
> http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
> HTH,
> Robert
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
>> I have a matrix that shows sales per year (rows) and month (columns).
>> If the data being used contains no records for a particular month for any
>> year (e.g. November), that column (i.e. column 11) is completely missing
>> from the matrix.
>> Can the matrix be configured to always show a given set of columns, even
> if
>> there is no underlying data?
>> Thanks
>>
>

Matrix region formulas

Hi,
I would like a Matrix report with
Jan, Feb, Mar, Total on columns
where total sums up all the month values in that row.
And something like
2003, 2004, Difference2003-2004 on columns
AddColumn does't seem to do the trick
Thanks!Sorry, the first one I see I can do with the "Subtotal" option but what
about "average for the subtotal
"BoruRR" wrote:
> Hi,
> I would like a Matrix report with
> Jan, Feb, Mar, Total on columns
> where total sums up all the month values in that row.
> And something like
> 2003, 2004, Difference2003-2004 on columns
> AddColumn does't seem to do the trick
> Thanks!

Matrix Page Breaks within a list / Visibility is conditional

Think I have found a bug.

I have a report that has a Parameter called "LevelOfDetail" This has 2 possible values "Summary, Detail, or Combined".

The report has a Summary Section and a subreport that holds the details.
WIthin the summary section is a Matrix (a list of all properties and some values)

If they choose to see the details, the matrix in the details section will show a break out of all this information summarized in the summary section.

The Details Matrix is set to have a page break at the beginning and end of the top level group.

I have a list control on the summary page that contains the details subreport and passes the appropriate parameter.

Everything works the way I want until I try to set visibility on sub report.

Once I set the conditional visibility of the report objects (based on the Level of Detail parameter) the page breaks are not recognized.

This is important as the user will never print the report, but will be downloading to Excel.

If the page breaks work correctly, each page is assigned a different worksheet in their downloaded workbook.

Any help, please let me know.

Page break and visibility condition can not be used for same componenet. I mean to say they are not supported by SSRS 2005. u need to go for a work around.

Priyank

Matrix - Total in non column group

Is it possible to create a "total" for a non "column group" ?
Usually you add a "SubTotal" to get values in column groups summarized. Is
it possible to do something similar to a "row group column"?
Like:
DataTable
"Q1", Id1, "NonGroup", 1, "Group1", 5
"Q1", Id1, "NonGroup", 1, "Group2", 6
"Q1", Id2, "NonGroup", 1, "Group1", 7
"Q1", Id2, "NonGroup", 2, "Group2", 8
"Q1", Id2, "NonGroup", 2, "Group3", 9
Matrix
"Group1" "Group2" "Group3"
"Q1", 3 (1+2), 12 (5+7), 14 (6+8), 9You can try to right click on your row group and choose Subtotal on this
one.
Kaisa M. Lindahl Lervik
"Maran" <Maran@.discussions.microsoft.com> wrote in message
news:7AA486D8-CB21-43D3-84E5-2AC99AED3801@.microsoft.com...
> Is it possible to create a "total" for a non "column group" ?
> Usually you add a "SubTotal" to get values in column groups summarized. Is
> it possible to do something similar to a "row group column"?
> Like:
> DataTable
> "Q1", Id1, "NonGroup", 1, "Group1", 5
> "Q1", Id1, "NonGroup", 1, "Group2", 6
> "Q1", Id2, "NonGroup", 1, "Group1", 7
> "Q1", Id2, "NonGroup", 2, "Group2", 8
> "Q1", Id2, "NonGroup", 2, "Group3", 9
> Matrix
> "Group1" "Group2" "Group3"
> "Q1", 3 (1+2), 12 (5+7), 14 (6+8), 9
>|||Thank you for your response Kaisa.
I think I need to clearify my wished result, though.
If I do like suggested I get the total of all rows, 7. I like the result to
be 3, the total of the unique rows.
It might be a combination of grouping and "total".
Any suggestion welcome.
Martin Bring (Sogeti AB)
***********************
"Kaisa M. Lindahl Lervik" wrote:
> You can try to right click on your row group and choose Subtotal on this
> one.
> Kaisa M. Lindahl Lervik
> "Maran" <Maran@.discussions.microsoft.com> wrote in message
> news:7AA486D8-CB21-43D3-84E5-2AC99AED3801@.microsoft.com...
> > Is it possible to create a "total" for a non "column group" ?
> >
> > Usually you add a "SubTotal" to get values in column groups summarized. Is
> > it possible to do something similar to a "row group column"?
> >
> > Like:
> > DataTable
> > "Q1", Id1, "NonGroup", 1, "Group1", 5
> > "Q1", Id1, "NonGroup", 1, "Group2", 6
> > "Q1", Id2, "NonGroup", 1, "Group1", 7
> > "Q1", Id2, "NonGroup", 2, "Group2", 8
> > "Q1", Id2, "NonGroup", 2, "Group3", 9
> >
> > Matrix
> > "Group1" "Group2" "Group3"
> > "Q1", 3 (1+2), 12 (5+7), 14 (6+8), 9
> >
>
>|||Just a question:
The third row in your list, is that supposed to be Id1 or Id2?
Have you looked at the InScope function? This can be used to set the scope
of the sum function, so that you can choose what group to sum in your
subtotal.
You might also want to look at the RunningValue function, with SUM and the
correct group as scope.
Kaisa M. Lindahl Lervik
"Maran" <Maran@.discussions.microsoft.com> wrote in message
news:082DE84D-0126-4892-8306-4676D390DE9A@.microsoft.com...
> Thank you for your response Kaisa.
> I think I need to clearify my wished result, though.
> If I do like suggested I get the total of all rows, 7. I like the result
> to
> be 3, the total of the unique rows.
> It might be a combination of grouping and "total".
> Any suggestion welcome.
> Martin Bring (Sogeti AB)
> ***********************
> "Kaisa M. Lindahl Lervik" wrote:
>> You can try to right click on your row group and choose Subtotal on this
>> one.
>> Kaisa M. Lindahl Lervik
>> "Maran" <Maran@.discussions.microsoft.com> wrote in message
>> news:7AA486D8-CB21-43D3-84E5-2AC99AED3801@.microsoft.com...
>> > Is it possible to create a "total" for a non "column group" ?
>> >
>> > Usually you add a "SubTotal" to get values in column groups summarized.
>> > Is
>> > it possible to do something similar to a "row group column"?
>> >
>> > Like:
>> > DataTable
>> > "Q1", Id1, "NonGroup", 1, "Group1", 5
>> > "Q1", Id1, "NonGroup", 1, "Group2", 6
>> > "Q1", Id2, "NonGroup", 1, "Group1", 7
>> > "Q1", Id2, "NonGroup", 2, "Group2", 8
>> > "Q1", Id2, "NonGroup", 2, "Group3", 9
>> >
>> > Matrix
>> > "Group1" "Group2" "Group3"
>> > "Q1", 3 (1+2), 12 (5+7), 14 (6+8), 9
>> >
>>

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

Matching multiple columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...Kuttan
SELECT cisco .*, ciscocom.*
FROM cisco
FULL OUTER JOIN
ciscocom
ON cisco .c1 = ciscocom.c1
AND cisco .c2 = ciscocom.c2
..
AND cisco .cn = ciscocom.cn
WHERE cisco .key IS NULL OR ciscocom.key IS NULL
"Kuttan" <vvyshak@.gmail.com> wrote in message
news:1141210889.041213.70320@.v46g2000cwv.googlegroups.com...
> Hi all..
> I have two tables such as cisco and ciscocom. and i wan to compare each
> row of ciscocom with cisco having same column values. i wan to get the
> count of matching columns for each row in cisco...
> eg:
> Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
> columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
> for each row in ciscocom. please provide me with the procedure...
> Waiting for your response...
>|||thanks a lot..
But it doesnt worked.
Actually i wan the columns that matches with each rows of the cisco
from ciscocom...
Anyway thanx a lot for your response...
thank u so much..|||Hope this is what you're looking for
SELECT COUNT (cisco.Products)
FROM cisco INNER JOIN ciscocom
ON
cisco.Products=cisco.Products AND
cisco.fw=cisco.fw AND
cisco.ports=cisco.ports
/*
* If you want to specify a value for a column
*
*/
WHERE cisco.Products='your_value'

Matching multiple columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...On 1 Mar 2006 03:01:21 -0800, Kuttan wrote:

>Hi all..
>I have two tables such as cisco and ciscocom. and i wan to compare each
>row of ciscocom with cisco having same column values. i wan to get the
>count of matching columns for each row in cisco...
>eg:
>Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
>columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
>for each row in ciscocom. please provide me with the procedure...
>Waiting for your response...
Hi Kuttan,
Not sure if I fully understand your requirements. If the answer below is
not what you're looking for, then please check www.aspfaq.com/5006 to
find out how to post CREATE TABLE statements, INSERT statements and
required results in order to get better help.
SELECT a.KeyColumn,
CASE WHEN a.DataColumn1 = b.DataColumn1 THEN 1 ELSE 0 END
+ CASE WHEN a.DataColumn2 = b.DataColumn2 THEN 1 ELSE 0 END
.....
+ CASE WHEN a.DataColumnN = b.DataColumnN THEN 1 ELSE 0 END
AS MatchCount
FROM Table1 AS a
INNER JOIN Table2 AS b
ON a.KeyColumn = b.KeyColumn
Hugo Kornelis, SQL Server MVP

Master..sysdatabases.status column

Hi
sysdatabases.status column, is given some fixed values like:
WHEN status = 512 THEN 'offline'
WHEN status = 1024 THEN 'read only'
WHEN status = 2048 THEN 'dbo use only' and so on...
However when I put on the of the user databases in dbo use only, and I query
the
select * from master..sysdatabases
Then the value for status column for that database is not 2048, but it is
4112.
How do I interpret this code'
I am trying to write some job based on the status of the database.Actually, that's a bitmask -- multiple bits can be on at the same time.
So it's really:
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 1024) <> 0 THEN 'read only'
... etc
Here is the complete list (cut from BOL):
1 = autoclose; set with ALTER DATABASE.
4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
16 = torn page detection, set with ALTER DATABASE.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with ALTER DATABASE.
1024 = read only; set with ALTER DATABASE.
2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
4096 = single user; set with ALTER DATABASE.
32768 = emergency mode.
4194304 = autoshrink , set with ALTER DATABASE.
1073741824 = cleanly shutdown.
4112 = 4096 + 16 = torn page detection and single user ... not dbo use only
according to this?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:C8D00A32-ADE7-4601-8297-648801AB1727@.microsoft.com...
> Hi
> sysdatabases.status column, is given some fixed values like:
> WHEN status = 512 THEN 'offline'
> WHEN status = 1024 THEN 'read only'
> WHEN status = 2048 THEN 'dbo use only' and so on...
> However when I put on the of the user databases in dbo use only, and I
query
> the
> select * from master..sysdatabases
> Then the value for status column for that database is not 2048, but it is
> 4112.
> How do I interpret this code'
> I am trying to write some job based on the status of the database.
>
>

Master..sysdatabases.status column

Hi
sysdatabases.status column, is given some fixed values like:
WHEN status = 512 THEN 'offline'
WHEN status = 1024 THEN 'read only'
WHEN status = 2048 THEN 'dbo use only'and so on...
However when I put on the of the user databases in dbo use only, and I query
the
select * from master..sysdatabases
Then the value for status column for that database is not 2048, but it is
4112.
How do I interpret this code?
I am trying to write some job based on the status of the database.
Actually, that's a bitmask -- multiple bits can be on at the same time.
So it's really:
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 1024) <> 0 THEN 'read only'
... etc
Here is the complete list (cut from BOL):
1 = autoclose; set with ALTER DATABASE.
4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
16 = torn page detection, set with ALTER DATABASE.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with ALTER DATABASE.
1024 = read only; set with ALTER DATABASE.
2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
4096 = single user; set with ALTER DATABASE.
32768 = emergency mode.
4194304 = autoshrink , set with ALTER DATABASE.
1073741824 = cleanly shutdown.
4112 = 4096 + 16 = torn page detection and single user ... not dbo use only
according to this?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:C8D00A32-ADE7-4601-8297-648801AB1727@.microsoft.com...
> Hi
> sysdatabases.status column, is given some fixed values like:
> WHEN status = 512 THEN 'offline'
> WHEN status = 1024 THEN 'read only'
> WHEN status = 2048 THEN 'dbo use only' and so on...
> However when I put on the of the user databases in dbo use only, and I
query
> the
> select * from master..sysdatabases
> Then the value for status column for that database is not 2048, but it is
> 4112.
> How do I interpret this code?
> I am trying to write some job based on the status of the database.
>
>

Master..sysdatabases.status column

Hi
sysdatabases.status column, is given some fixed values like:
WHEN status = 512 THEN 'offline'
WHEN status = 1024 THEN 'read only'
WHEN status = 2048 THEN 'dbo use only' and so on...
However when I put on the of the user databases in dbo use only, and I query
the
select * from master..sysdatabases
Then the value for status column for that database is not 2048, but it is
4112.
How do I interpret this code'
I am trying to write some job based on the status of the database.Actually, that's a bitmask -- multiple bits can be on at the same time.
So it's really:
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 1024) <> 0 THEN 'read only'
... etc
Here is the complete list (cut from BOL):
1 = autoclose; set with ALTER DATABASE.
4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
16 = torn page detection, set with ALTER DATABASE.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with ALTER DATABASE.
1024 = read only; set with ALTER DATABASE.
2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
4096 = single user; set with ALTER DATABASE.
32768 = emergency mode.
4194304 = autoshrink , set with ALTER DATABASE.
1073741824 = cleanly shutdown.
4112 = 4096 + 16 = torn page detection and single user ... not dbo use only
according to this?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:C8D00A32-ADE7-4601-8297-648801AB1727@.microsoft.com...
> Hi
> sysdatabases.status column, is given some fixed values like:
> WHEN status = 512 THEN 'offline'
> WHEN status = 1024 THEN 'read only'
> WHEN status = 2048 THEN 'dbo use only' and so on...
> However when I put on the of the user databases in dbo use only, and I
query
> the
> select * from master..sysdatabases
> Then the value for status column for that database is not 2048, but it is
> 4112.
> How do I interpret this code'
> I am trying to write some job based on the status of the database.
>
>