median query without using function

I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
108, sports, 11000
109, luxury, 9000
i need to write a query that WITHOUT USING A FUNCTION will return the
median selling price for each class of car. result should look like:
Class, Med_Price
luxury, 9000
sports, 11000
thanks to all u SQLers
>From your sample data it looks like you want the most commonly
occurring price for each vehicle (I thought this was
called the "mode", not the "median").
CREATE TABLE Cars(VIN INT,Class VARCHAR(10),sell_price INT)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(101, 'sports', 10000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(102, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(103, 'luxury', 9000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(104, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(105, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(106, 'luxury', 5000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(107, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(108, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(109, 'luxury', 9000)
GO
CREATE VIEW CarNums
AS
SELECT Class,sell_price,COUNT(*) as Num
FROM Cars
GROUP BY Class,sell_price
GO
SELECT c1.Class,c1.sell_price AS Med_Price
FROM CarNums c1
INNER JOIN (
SELECT Class,MAX(Num)
FROM CarNums
GROUP BY Class) C2(Class,Num) ON C2.Class=C1.Class
AND C2.Num=C1.Num
|||My mistake, you can get the medians using this
CREATE VIEW CarRank
AS
SELECT c1.VIN,
c1.Class,
c1.sell_price,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class
AND ((c2.sell_price<c1.sell_price)
OR (c2.sell_price=c1.sell_price AND c2.VIN<=c1.VIN))) as
Rank,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class) as MaxRank
FROM Cars c1
GO
SELECT Class,AVG(sell_price) AS Med_Price
FROM CarRank
WHERE Rank IN ((MaxRank+1)/2,(MaxRank/2)+1)
GROUP BY Class
|||I have a whoel chapter on verious ways to do this in SQL FOR SMARTIES.
Here is one answer.
Median with Characteristic Function
Anatoly Abramovich, Yelena Alexandrova, and Eugene Birger presented a
series of articles in SQL Forum magazine on computing the median (SQL
Forum 1993, 1994). They define a characteristic function, which they
call delta, using the Sybase sign() function. The delta or
characteristic function accepts a Boolean expression as an argument and
returns a 1 if it is TRUE and a zero if it is FALSE or UNKNOWN.
In SQL-92 we have a CASE expression, which can be used to construct the
delta function. This is new to SQL-92, but you can find vendor
functions of the form IF...THEN...ELSE that behave like the condition
expression in Algol or like the question markPcolon operator in C.
The authors also distinguish between the statistical median, whose
value must be a member of the set, and the financial median, whose
value is the average of the middle two members of the set. A
statistical median exists when there is an odd number of items in the
set. If there is an even number of items, you must decide if you want
to use the highest value in the lower half (they call this the left
median) or the lowest value in the upper half (they call this the right
median).
The left statistical median of a unique column can be found with this
query:
SELECT P1.bin
FROM Parts AS P1, Parts AS P2
GROUP BY P1.bin
HAVING SUM(CASE WHEN (P2.bin <= P1.bin) THEN 1 ELSE 0 END)
= (COUNT(*) + 1) / 2;
Changing the direction of the theta test in the HAVING clause will
allow you to pick the right statistical median if a central element
does not exist in the set. You will also notice something else about
the median of a set of unique values: It is usually meaningless. What
does the median bin number mean, anyway? A good rule of thumb is that
if it does not make sense as an average, it does not make sense as a
median.
The statistical median of a column with duplicate values can be found
with a query based on the same ideas, but you have to adjust the HAVING
clause to allow for overlap; thus, the left statistical median is found
by
SELECT P1.weight
FROM Parts AS P1, Parts AS P2
GROUP BY P1.weight
HAVING SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1);
Notice that here the left and right medians can be the same, so there
is no need to pick one over the other in many of the situations where
you have an even number of items. Switching the comparison operators in
the two CASE expressions will give you the right statistical median.
The author's query for the financial median depends on some Sybase
features that cannot be found in other products, so I would recommend
using a combination of the right and left statistical medians to return
a set of values about the center of the data, and then averaging them,
thus:
SELECT AVG(P1.weight)
FROM Parts AS P1, Parts AS P2
HAVING (SUM(CASE WHEN P2.weight <= P1.weight -- left median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1))
OR (SUM(CASE WHEN P2.weight >= P1.weight -- right median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1));
An optimizer may be able to reduce this expression internally, since
the expressions involved with COUNT(*) are constants. This entire query
could be put into a FROM clause and the average taken of the one or two
rows in the result to find the financial median. In SQL-89, you would
have to define this as a VIEW and then take the average.
If you have SQL-2005, you can try something like (untested):
SELECT AVG(x),
ROW_NUMBER () OVER (ORDER BY x ASC) AS hi,
ROW_NUMBER () OVER (ORDER BY x DESC) AS lo,
FROM Foobar
WHERE hi IN (lo, lo-1, lo+1);
|||i don't do homework for college students.

median query without using function

I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
108, sports, 11000
109, luxury, 9000
i need to write a query that WITHOUT USING A FUNCTION will return the
median selling price for each class of car. result should look like:
Class, Med_Price
luxury, 9000
sports, 11000
thanks to all u SQLers>From your sample data it looks like you want the most commonly
occurring price for each vehicle (I thought this was
called the "mode", not the "median").
CREATE TABLE Cars(VIN INT,Class VARCHAR(10),sell_price INT)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(101, 'sports', 10000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(102, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(103, 'luxury', 9000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(104, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(105, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(106, 'luxury', 5000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(107, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(108, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(109, 'luxury', 9000)
GO
CREATE VIEW CarNums
AS
SELECT Class,sell_price,COUNT(*) as Num
FROM Cars
GROUP BY Class,sell_price
GO
SELECT c1.Class,c1.sell_price AS Med_Price
FROM CarNums c1
INNER JOIN (
SELECT Class,MAX(Num)
FROM CarNums
GROUP BY Class) C2(Class,Num) ON C2.Class=C1.Class
AND C2.Num=C1.Num|||My mistake, you can get the medians using this
CREATE VIEW CarRank
AS
SELECT c1.VIN,
c1.Class,
c1.sell_price,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class
AND ((c2.sell_price<c1.sell_price)
OR (c2.sell_price=c1.sell_price AND c2.VIN<=c1.VIN))) as
Rank,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class) as MaxRank
FROM Cars c1
GO
SELECT Class,AVG(sell_price) AS Med_Price
FROM CarRank
WHERE Rank IN ((MaxRank+1)/2,(MaxRank/2)+1)
GROUP BY Class|||I have a whoel chapter on verious ways to do this in SQL FOR SMARTIES.
Here is one answer.
Median with Characteristic Function
Anatoly Abramovich, Yelena Alexandrova, and Eugene Birger presented a
series of articles in SQL Forum magazine on computing the median (SQL
Forum 1993, 1994). They define a characteristic function, which they
call delta, using the Sybase sign() function. The delta or
characteristic function accepts a Boolean expression as an argument and
returns a 1 if it is TRUE and a zero if it is FALSE or UNKNOWN.
In SQL-92 we have a CASE expression, which can be used to construct the
delta function. This is new to SQL-92, but you can find vendor
functions of the form IF...THEN...ELSE that behave like the condition
expression in Algol or like the question markPcolon operator in C.
The authors also distinguish between the statistical median, whose
value must be a member of the set, and the financial median, whose
value is the average of the middle two members of the set. A
statistical median exists when there is an odd number of items in the
set. If there is an even number of items, you must decide if you want
to use the highest value in the lower half (they call this the left
median) or the lowest value in the upper half (they call this the right
median).
The left statistical median of a unique column can be found with this
query:
SELECT P1.bin
FROM Parts AS P1, Parts AS P2
GROUP BY P1.bin
HAVING SUM(CASE WHEN (P2.bin <= P1.bin) THEN 1 ELSE 0 END)
= (COUNT(*) + 1) / 2;
Changing the direction of the theta test in the HAVING clause will
allow you to pick the right statistical median if a central element
does not exist in the set. You will also notice something else about
the median of a set of unique values: It is usually meaningless. What
does the median bin number mean, anyway? A good rule of thumb is that
if it does not make sense as an average, it does not make sense as a
median.
The statistical median of a column with duplicate values can be found
with a query based on the same ideas, but you have to adjust the HAVING
clause to allow for overlap; thus, the left statistical median is found
by
SELECT P1.weight
FROM Parts AS P1, Parts AS P2
GROUP BY P1.weight
HAVING SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1);
Notice that here the left and right medians can be the same, so there
is no need to pick one over the other in many of the situations where
you have an even number of items. Switching the comparison operators in
the two CASE expressions will give you the right statistical median.
The author's query for the financial median depends on some Sybase
features that cannot be found in other products, so I would recommend
using a combination of the right and left statistical medians to return
a set of values about the center of the data, and then averaging them,
thus:
SELECT AVG(P1.weight)
FROM Parts AS P1, Parts AS P2
HAVING (SUM(CASE WHEN P2.weight <= P1.weight -- left median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1))
OR (SUM(CASE WHEN P2.weight >= P1.weight -- right median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1));
An optimizer may be able to reduce this expression internally, since
the expressions involved with COUNT(*) are constants. This entire query
could be put into a FROM clause and the average taken of the one or two
rows in the result to find the financial median. In SQL-89, you would
have to define this as a VIEW and then take the average.
If you have SQL-2005, you can try something like (untested):
SELECT AVG(x),
ROW_NUMBER () OVER (ORDER BY x ASC) AS hi,
ROW_NUMBER () OVER (ORDER BY x DESC) AS lo,
FROM Foobar
WHERE hi IN (lo, lo-1, lo+1);|||i don't do homework for college students.sql

median query without using function

I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
108, sports, 11000
109, luxury, 9000
i need to write a query that WITHOUT USING A FUNCTION will return the
median selling price for each class of car. result should look like:
Class, Med_Price
luxury, 9000
sports, 11000
thanks to all u SQLers>From your sample data it looks like you want the most commonly
occurring price for each vehicle (I thought this was
called the "mode", not the "median").
CREATE TABLE Cars(VIN INT,Class VARCHAR(10),sell_price INT)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(101, 'sports', 10000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(102, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(103, 'luxury', 9000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(104, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(105, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(106, 'luxury', 5000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(107, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(108, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(109, 'luxury', 9000)
GO
CREATE VIEW CarNums
AS
SELECT Class,sell_price,COUNT(*) as Num
FROM Cars
GROUP BY Class,sell_price
GO
SELECT c1.Class,c1.sell_price AS Med_Price
FROM CarNums c1
INNER JOIN (
SELECT Class,MAX(Num)
FROM CarNums
GROUP BY Class) C2(Class,Num) ON C2.Class=C1.Class
AND C2.Num=C1.Num|||My mistake, you can get the medians using this
CREATE VIEW CarRank
AS
SELECT c1.VIN,
c1.Class,
c1.sell_price,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class
AND ((c2.sell_price<c1.sell_price)
OR (c2.sell_price=c1.sell_price AND c2.VIN<=c1.VIN))) as
Rank,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class) as MaxRank
FROM Cars c1
GO
SELECT Class,AVG(sell_price) AS Med_Price
FROM CarRank
WHERE Rank IN ((MaxRank+1)/2,(MaxRank/2)+1)
GROUP BY Class|||I have a whoel chapter on verious ways to do this in SQL FOR SMARTIES.
Here is one answer.
Median with Characteristic Function
Anatoly Abramovich, Yelena Alexandrova, and Eugene Birger presented a
series of articles in SQL Forum magazine on computing the median (SQL
Forum 1993, 1994). They define a characteristic function, which they
call delta, using the Sybase sign() function. The delta or
characteristic function accepts a Boolean expression as an argument and
returns a 1 if it is TRUE and a zero if it is FALSE or UNKNOWN.
In SQL-92 we have a CASE expression, which can be used to construct the
delta function. This is new to SQL-92, but you can find vendor
functions of the form IF...THEN...ELSE that behave like the condition
expression in Algol or like the question markPcolon operator in C.
The authors also distinguish between the statistical median, whose
value must be a member of the set, and the financial median, whose
value is the average of the middle two members of the set. A
statistical median exists when there is an odd number of items in the
set. If there is an even number of items, you must decide if you want
to use the highest value in the lower half (they call this the left
median) or the lowest value in the upper half (they call this the right
median).
The left statistical median of a unique column can be found with this
query:
SELECT P1.bin
FROM Parts AS P1, Parts AS P2
GROUP BY P1.bin
HAVING SUM(CASE WHEN (P2.bin <= P1.bin) THEN 1 ELSE 0 END)
= (COUNT(*) + 1) / 2;
Changing the direction of the theta test in the HAVING clause will
allow you to pick the right statistical median if a central element
does not exist in the set. You will also notice something else about
the median of a set of unique values: It is usually meaningless. What
does the median bin number mean, anyway? A good rule of thumb is that
if it does not make sense as an average, it does not make sense as a
median.
The statistical median of a column with duplicate values can be found
with a query based on the same ideas, but you have to adjust the HAVING
clause to allow for overlap; thus, the left statistical median is found
by
SELECT P1.weight
FROM Parts AS P1, Parts AS P2
GROUP BY P1.weight
HAVING SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1);
Notice that here the left and right medians can be the same, so there
is no need to pick one over the other in many of the situations where
you have an even number of items. Switching the comparison operators in
the two CASE expressions will give you the right statistical median.
The author's query for the financial median depends on some Sybase
features that cannot be found in other products, so I would recommend
using a combination of the right and left statistical medians to return
a set of values about the center of the data, and then averaging them,
thus:
SELECT AVG(P1.weight)
FROM Parts AS P1, Parts AS P2
HAVING (SUM(CASE WHEN P2.weight <= P1.weight -- left median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1))
OR (SUM(CASE WHEN P2.weight >= P1.weight -- right median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1));
An optimizer may be able to reduce this expression internally, since
the expressions involved with COUNT(*) are constants. This entire query
could be put into a FROM clause and the average taken of the one or two
rows in the result to find the financial median. In SQL-89, you would
have to define this as a VIEW and then take the average.
If you have SQL-2005, you can try something like (untested):
SELECT AVG(x),
ROW_NUMBER () OVER (ORDER BY x ASC) AS hi,
ROW_NUMBER () OVER (ORDER BY x DESC) AS lo,
FROM Foobar
WHERE hi IN (lo, lo-1, lo+1);|||i don't do homework for college students.

median query without using function

I have a table (cars) with 3 fields:

VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
108, sports, 11000
109, luxury, 9000

i need to write a query that WITHOUT USING A FUNCTION will return the
median selling price for each class of car. result should look like:

Class, Med_Price
luxury, 9000
sports, 11000

thanks to all u SQLers>From your sample data it looks like you want the most commonly
occurring price for each vehicle (I thought this was
called the "mode", not the "median").

CREATE TABLE Cars(VIN INT,Class VARCHAR(10),sell_price INT)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(101, 'sports', 10000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(102, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(103, 'luxury', 9000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(104, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(105, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(106, 'luxury', 5000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(107, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(108, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(109, 'luxury', 9000)
GO
CREATE VIEW CarNums
AS
SELECT Class,sell_price,COUNT(*) as Num
FROM Cars
GROUP BY Class,sell_price

GO

SELECT c1.Class,c1.sell_price AS Med_Price
FROM CarNums c1
INNER JOIN (
SELECT Class,MAX(Num)
FROM CarNums
GROUP BY Class) C2(Class,Num) ON C2.Class=C1.Class
AND C2.Num=C1.Num|||My mistake, you can get the medians using this

CREATE VIEW CarRank
AS
SELECT c1.VIN,
c1.Class,
c1.sell_price,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class
AND ((c2.sell_price<c1.sell_price)
OR (c2.sell_price=c1.sell_price AND c2.VIN<=c1.VIN))) as
Rank,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class) as MaxRank
FROM Cars c1

GO

SELECT Class,AVG(sell_price) AS Med_Price
FROM CarRank
WHERE Rank IN ((MaxRank+1)/2,(MaxRank/2)+1)
GROUP BY Class|||I have a whoel chapter on verious ways to do this in SQL FOR SMARTIES.
Here is one answer.

Median with Characteristic Function

Anatoly Abramovich, Yelena Alexandrova, and Eugene Birger presented a
series of articles in SQL Forum magazine on computing the median (SQL
Forum 1993, 1994). They define a characteristic function, which they
call delta, using the Sybase sign() function. The delta or
characteristic function accepts a Boolean expression as an argument and
returns a 1 if it is TRUE and a zero if it is FALSE or UNKNOWN.

In SQL-92 we have a CASE expression, which can be used to construct the
delta function. This is new to SQL-92, but you can find vendor
functions of the form IF...THEN...ELSE that behave like the condition
expression in Algol or like the question markPcolon operator in C.

The authors also distinguish between the statistical median, whose
value must be a member of the set, and the financial median, whose
value is the average of the middle two members of the set. A
statistical median exists when there is an odd number of items in the
set. If there is an even number of items, you must decide if you want
to use the highest value in the lower half (they call this the left
median) or the lowest value in the upper half (they call this the right
median).

The left statistical median of a unique column can be found with this
query:

SELECT P1.bin
FROM Parts AS P1, Parts AS P2
GROUP BY P1.bin
HAVING SUM(CASE WHEN (P2.bin <= P1.bin) THEN 1 ELSE 0 END)
= (COUNT(*) + 1) / 2;

Changing the direction of the theta test in the HAVING clause will
allow you to pick the right statistical median if a central element
does not exist in the set. You will also notice something else about
the median of a set of unique values: It is usually meaningless. What
does the median bin number mean, anyway? A good rule of thumb is that
if it does not make sense as an average, it does not make sense as a
median.

The statistical median of a column with duplicate values can be found
with a query based on the same ideas, but you have to adjust the HAVING
clause to allow for overlap; thus, the left statistical median is found
by

SELECT P1.weight
FROM Parts AS P1, Parts AS P2
GROUP BY P1.weight
HAVING SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1);

Notice that here the left and right medians can be the same, so there
is no need to pick one over the other in many of the situations where
you have an even number of items. Switching the comparison operators in
the two CASE expressions will give you the right statistical median.

The author's query for the financial median depends on some Sybase
features that cannot be found in other products, so I would recommend
using a combination of the right and left statistical medians to return
a set of values about the center of the data, and then averaging them,
thus:

SELECT AVG(P1.weight)
FROM Parts AS P1, Parts AS P2
HAVING (SUM(CASE WHEN P2.weight <= P1.weight -- left median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1))
OR (SUM(CASE WHEN P2.weight >= P1.weight -- right median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1));

An optimizer may be able to reduce this expression internally, since
the expressions involved with COUNT(*) are constants. This entire query
could be put into a FROM clause and the average taken of the one or two
rows in the result to find the financial median. In SQL-89, you would
have to define this as a VIEW and then take the average.

If you have SQL-2005, you can try something like (untested):

SELECT AVG(x),
ROW_NUMBER () OVER (ORDER BY x ASC) AS hi,
ROW_NUMBER () OVER (ORDER BY x DESC) AS lo,
FROM Foobar
WHERE hi IN (lo, lo-1, lo+1);|||i don't do homework for college students.

Median query with Group by clause

Hi,

I have a view in which I have 3 cols...(pno,ptno,diff)..diff is the
difference in time in minutes.I want to calculate Median(diff) group
by pno,ptno...using a sql query for SQL server...

Any help is greatly appreciated..

Thanks

AJ"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0404211014.79b1e842@.posting.google.c om...
> Hi,
> I have a view in which I have 3 cols...(pno,ptno,diff)..diff is the
> difference in time in minutes.I want to calculate Median(diff) group
> by pno,ptno...using a sql query for SQL server...
> Any help is greatly appreciated..
> Thanks
> AJ

http://groups.google.com/groups?hl=...80%40ietsmet.nl

Simon

median function

I have a long term need for a median function, I was wondering if anyone has or knows of some code or user defined functions somewhere that would do this. Ideally you could use it just liket the rest of the aggregate functions like AVG, etc.Hi Bill

You would need some sort of CLR code to use it like AVG etc.

The SQL in the link below works (I translated it into Access SQL recently) however you will need to make minor modifications to account for nulls, not use the financial median etc.
http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html

HTHsql

Median Function

I am trying to write a function to give me a median value. I have a matrix.
There is a column group/text box called loanbalance. Displayed in that
field is the SUM of all loan balances for the month.
I have another textbox called "Median". This textbox I want to display the
median value for the values in the "Loanbalance" field/textbox.
I don't have detail rows showing in the report, I am grouping.
I went out to the menu and selected: report, report properties, code, and
tried to write a function MEDIAN(reportitems!loanbalance.value), end
function. I know pretty basic, but I am not an advanced user.
Then in the "median" textbox, expression I have = code.MEDIAN(ReportItems!loanbalance.value).
It does not work. I don't get errors, I just don't get anything back for a
value.
Could someone help me out with this? Am I going about this the right way?
Thanks,Susan,
I have a report where I needed to get the Median time for documents
processed and ended up writing a routine from within a stored procedure to
perform this operation. As of yet I have not found a way to do it from w/in
RS - if anybody out there knows of way to accomplish this your help would be
appreciated.
Basically what I did was to get the row number corresponding to the number
of documents processed and divide that number by 2. I then took the
resulting number as parameter for my where clause-
Ex:
Set @.RowNumber = @.RowNumber / 2
Select @.MedianTime = DocTime From @.ReportDataTable
Where RowNumber = @.RowNumber
I know it's rudimentary but it does work.
Hope this helps.
Bill Youngman
Anexinet, Inc.
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:F2635C55-394A-44FD-A8C0-934C42260417@.microsoft.com...
> I am trying to write a function to give me a median value. I have a
matrix.
> There is a column group/text box called loanbalance. Displayed in that
> field is the SUM of all loan balances for the month.
> I have another textbox called "Median". This textbox I want to display
the
> median value for the values in the "Loanbalance" field/textbox.
> I don't have detail rows showing in the report, I am grouping.
> I went out to the menu and selected: report, report properties, code, and
> tried to write a function MEDIAN(reportitems!loanbalance.value), end
> function. I know pretty basic, but I am not an advanced user.
> Then in the "median" textbox, expression I have => code.MEDIAN(ReportItems!loanbalance.value).
> It does not work. I don't get errors, I just don't get anything back for
a
> value.
> Could someone help me out with this? Am I going about this the right way?
> Thanks,
>

Median Calculation - Analysis Services

A really simple question,,,, I need to calculate the median of a
measure. I'm having a tough time to figure out which dimension to use.
If If I have 4 dimensions, Date, region, site, and process code and I
want to get the median for the measure number of days, what do I use to
define the set?
median ([Process].AllMembers,measures.[No Of Days]) this is not working,
but at least the syntax was clean
standard median prompt
Median (Set[, Numeric Expression])
As I select different subsets of region, site and process code I
obviously want the median of No of days to be dynamic.
Stuck here and waiting anxiously for some bright person to shed the
light!!!
thanks,,,
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I would try the sqlserver.olap group with this one. This is more the
relational engine area.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Peter Weiler" <pweiler@.rogers.com> wrote in message
news:eLlFlRDIFHA.572@.tk2msftngp13.phx.gbl...
> A really simple question,,,, I need to calculate the median of a
> measure. I'm having a tough time to figure out which dimension to use.
> If If I have 4 dimensions, Date, region, site, and process code and I
> want to get the median for the measure number of days, what do I use to
> define the set?
> median ([Process].AllMembers,measures.[No Of Days]) this is not working,
> but at least the syntax was clean
> standard median prompt
> Median (Set[, Numeric Expression])
> As I select different subsets of region, site and process code I
> obviously want the median of No of days to be dynamic.
> Stuck here and waiting anxiously for some bright person to shed the
> light!!!
> thanks,,,
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Median calculation

I want to calculate the MEDIAN time interval between the two date field. In an OLAP cube one of the dimension consists of the two date fields as well as Interval(days) field (calculated at the data source view). How to create a Measure in the OLAP cube to calculate the MEDIAN Interval?

MEDIAN( <<Set>>[, <<Numeric Expression>>])

Since I'm not sure of the details your dimension, here's an Adventure Works sample query, for the Median duration in days of a Promotion:

>>

With Member [Measures].[MedPromoDays] as

Median(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Promotion].Properties("Start Date", TYPED),

[Promotion].[Promotion].Properties("End Date", TYPED)))

select {[Measures].[MedPromoDays]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

MedPromoDays
All Promotions 83.5
Discontinued Product 53
Excess Inventory 61
New Product 91
No Discount 1309
Seasonal Discount 30
Volume Discount 1095

>>

|||

And to slightly change Deepak's query to be more efficient:

With Member [Measures].[MedPromoDays] as

Median(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

[Promotion].[End Date].MemberValue))

select {[Measures].[MedPromoDays]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

Median Calc

I'm trying to use the following to calculate the median by year for the fiel
d
"labor_rate". This should calculate the median for any year where the count
is ODD. I haven't started on the "ELSE" part to calculate when the counts
are EVEN.
Anyway, the following doesn't work. I get an "incorrect syntax" error
message near line 3 and it doesn't like my order by clause either.
Any help would be greatly appreciated. Thanks.
SELECT [YYYY], median from
(SELECT [YYYY]
(SELECT TOP 1 [LABOR_RATE] FROM
(SELECT TOP 50 PERCENT [LABOR_RATE] , [YYYY]
FROM [dbo].[tbl_Data] WHERE [YYYY] = z.[YYYY]
order by labor_rate) sub
WHERE [YYYY] = z.[YYYY]
group by [YYYY], [LABOR_RATE]
having COUNT(*) % 2 <> 1
ORDER BY [LABOR_RATE] DESC))
as median
FROM [dbo].[tbl_Data] z
group by [YYYY], median
order by [YYYY]
CraigThere is a whole chapter on Medians in SQL FOR SMARTIES, with several
different methods.
If you have SQL-2005, you can use the row numbering to create an
ascending column and a descendng column with an OVER() clause.
The median is the row wher these two columns:
1) Are equal to each other (odd number of rows)
2) The average of the columns where they differ by one (even number of
rows) .
I have not tried this yet; I am haivng a XXXXX of time getting 2005 on
my machines for some reason.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||Craig,
You're missing a comma after [YYYY] in line 2. That's why the
error is in line 3, where the ( is, because that's where the error
becomes apparent.
I didn't look further.
Steve Kass
Drew University
Craig wrote:

>I'm trying to use the following to calculate the median by year for the fie
ld
>"labor_rate". This should calculate the median for any year where the coun
t
>is ODD. I haven't started on the "ELSE" part to calculate when the counts
>are EVEN.
>Anyway, the following doesn't work. I get an "incorrect syntax" error
>message near line 3 and it doesn't like my order by clause either.
>Any help would be greatly appreciated. Thanks.
>
>SELECT [YYYY], median from
>(SELECT [YYYY]
>(SELECT TOP 1 [LABOR_RATE] FROM
> (SELECT TOP 50 PERCENT [LABOR_RATE] , [YYYY]
> FROM [dbo].[tbl_Data] WHERE [YYYY] = z.[YYYY]
> order by labor_rate) sub
>WHERE [YYYY] = z.[YYYY]
>group by [YYYY], [LABOR_RATE]
>having COUNT(*) % 2 <> 1
>ORDER BY [LABOR_RATE] DESC))
>as median
>FROM [dbo].[tbl_Data] z
>group by [YYYY], median
>order by [YYYY]
>
>|||Thanks. That cleared up the error. But one error is left: There is
incorrect syntax in the "From" line...can't figure it out.
Any help would be greatly appreciated. Thanks.
--
Craig
"Steve Kass" wrote:

> Craig,
> You're missing a comma after [YYYY] in line 2. That's why the
> error is in line 3, where the ( is, because that's where the error
> becomes apparent.
> I didn't look further.
> Steve Kass
> Drew University
> Craig wrote:
>
>|||...it's the last "From" line, FYI..
--
Craig
"Craig" wrote:
> Thanks. That cleared up the error. But one error is left: There is
> incorrect syntax in the "From" line...can't figure it out.
> Any help would be greatly appreciated. Thanks.
> --
> Craig
>
> "Steve Kass" wrote:
>|||Craig,
If you format the code more clearly, you'll see:
SELECT [YYYY], median from (
SELECT
[YYYY],
(
SELECT TOP 1
[LABOR_RATE]
FROM (
SELECT TOP 50 PERCENT
[LABOR_RATE],
[YYYY]
FROM [dbo].[tbl_Data]
WHERE [YYYY] = z.[YYYY]
order by labor_rate
) sub
WHERE [YYYY] = z.[YYYY]
group by [YYYY], [LABOR_RATE]
having COUNT(*) % 2 <> 1
ORDER BY [LABOR_RATE] DESC
)
) as median
FROM [dbo].[tbl_Data] z
group by [YYYY], median
order by [YYYY]
Note what "as median" applies to. It's the name of a table,
not the name of a column, as you probably intend. This
query currently says "select stuff from T from U", basically.
Get rid of the ) before "as median", and add it before "z",
and get into the habit of reading your code, not just moving around
symbols until it stops generating syntax errors.
SK
Craig wrote:

>...it's the last "From" line, FYI..
>

median again

Can someone tell me where I am blowing it, I get
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
head. I know I am over looking the OBVIOUS, but it is not obvious to me.
I am trying to get the min, max, avg and MEDIAN of sales grouped by
customer, dollars in decending order.
Without the WITH MEMBER, the statement geives me min, max and avg perfectly,
HELP what am i missing out on here.
Thanks so much for you insight.
SQL 2000
George Collins
WITH MEMBER [INV History].[INV HIST Selling Price] as
'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
Price])
AS [Min], MAX([INV HIST Selling Price]) AS [Max],
AVG([INV HIST Selling Price]) AS [Avg]
FROM [INV History]
WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00', 102))
GROUP BY [INV HIST Cust Name]
ORDER BY SUM([INV HIST Selling Price]) DESC
Joe Celko always writes standard ANSI SQL. Existing products more or less
comply to the standard. In version 2000, T-SQL language used by SQL Server
does not support WITH clause yet. Check how to calculate the median in T-SQL
at http://www.aspfaq.com/show.asp?id=2506.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"george collins" <george@.nospan.com> wrote in message
news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
> Can someone tell me where I am blowing it, I get
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'WITH'.
> I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
> head. I know I am over looking the OBVIOUS, but it is not obvious to me.
> I am trying to get the min, max, avg and MEDIAN of sales grouped by
> customer, dollars in decending order.
> Without the WITH MEMBER, the statement geives me min, max and avg
perfectly,
> HELP what am i missing out on here.
> Thanks so much for you insight.
> SQL 2000
> George Collins
> WITH MEMBER [INV History].[INV HIST Selling Price] as
> 'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
> SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
> COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
> Price])
> AS [Min], MAX([INV HIST Selling Price]) AS [Max],
> AVG([INV HIST Selling Price]) AS [Avg]
> FROM [INV History]
> WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00',
102))
> GROUP BY [INV HIST Cust Name]
> ORDER BY SUM([INV HIST Selling Price]) DESC
>
|||The following is copied from the SQL 2000 Books online. "WITH" is part of
the example.
Am I missing something?
Thanks for you help
George
Topic last updated -- July 2003
Returns the median value of a numeric expression evaluated over a set.
Syntax
Median(Set[, Numeric Expression])
Remarks
The Median function returns the median value of a numeric expression that is
specified in Numeric Expression and evaluated over a set specified in
Set. The median value is the middle value in a set of ordered numbers
(unlike the mean value, which is the sum of a set of numbers divided by the
count of numbers in the set). The median value is determined by choosing the
smallest value such that at least half of the values in the set are no
greater than the chosen value. If the number of values within the set is
odd, the median value corresponds to a single value. If the number of values
within the set is even, the median value corresponds to the sum of the two
middle values divided by two.
Example
The following example, a calculated member that is executed against the
Sales cube of the FoodMart 2000 database, returns the median value of the
Unit Sales measure for the children of the Juice member in the Product
dimension:
WITH MEMBER [Measures].[MedianJuiceUnitSales] AS
'MEDIAN(Product.Juice.CHILDREN, Measures.[Unit Sales])'
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:Oeti76luEHA.3152@.TK2MSFTNGP14.phx.gbl...
> Joe Celko always writes standard ANSI SQL. Existing products more or less
> comply to the standard. In version 2000, T-SQL language used by SQL Server
> does not support WITH clause yet. Check how to calculate the median in
> T-SQL
> at http://www.aspfaq.com/show.asp?id=2506.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "george collins" <george@.nospan.com> wrote in message
> news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
> perfectly,
> 102))
>
begin 666 update_topic.gif
M1TE&.#EA$@.`6`/<`````````A ``_P!"0@."$A #_`$*$A(0`A(2$`(2$A(2$
M_\;&QO\``/__`/______________________________________________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_____________________RP`````$@.`6```(? `="!188*#!@.PX*$ARH$.'"
MA \=,H384"+#!046:-RHD6&!CQ@.3?E2XP$')@.P4K"CQYDF!(E0IBQEP8$B+"
M!0ILAI3)<V7.@.34EXC1XDJ=,GT0M(@.4JT.A,DS]7*H6:U('3GT.9*LTJU:K3
3I5TM<C4Y=2S'LQRC>KUJ-" `.P``
`
end
|||> Am I missing something?
Yes - this is part of MDX language, the language for browsing OLAP cubes in
Analysis Services, not par of the T-SQL language.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Okay, that answers the question. So I need to study, learn how to and make
a cube before this will work, or am I 100% out of the ball park.
I was trying really hard to make it work.
Thanks for the direction and information.
Always something new to learn.
George
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:ea%23El6quEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Yes - this is part of MDX language, the language for browsing OLAP cubes
> in
> Analysis Services, not par of the T-SQL language.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
sql

median again

Can someone tell me where I am blowing it, I get
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
head. I know I am over looking the OBVIOUS, but it is not obvious to me.
I am trying to get the min, max, avg and MEDIAN of sales grouped by
customer, dollars in decending order.
Without the WITH MEMBER, the statement geives me min, max and avg perfectly,
HELP what am i missing out on here.
Thanks so much for you insight.
SQL 2000
George Collins
WITH MEMBER [INV History].[INV HIST Selling Price] as
'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
Price])
AS [Min], MAX([INV HIST Selling Price]) AS [Max],
AVG([INV HIST Selling Price]) AS [Avg]
FROM [INV History]
WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00', 102))
GROUP BY [INV HIST Cust Name]
ORDER BY SUM([INV HIST Selling Price]) DESCJoe Celko always writes standard ANSI SQL. Existing products more or less
comply to the standard. In version 2000, T-SQL language used by SQL Server
does not support WITH clause yet. Check how to calculate the median in T-SQL
at http://www.aspfaq.com/show.asp?id=2506.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"george collins" <george@.nospan.com> wrote in message
news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
> Can someone tell me where I am blowing it, I get
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'WITH'.
> I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
> head. I know I am over looking the OBVIOUS, but it is not obvious to me.
> I am trying to get the min, max, avg and MEDIAN of sales grouped by
> customer, dollars in decending order.
> Without the WITH MEMBER, the statement geives me min, max and avg
perfectly,
> HELP what am i missing out on here.
> Thanks so much for you insight.
> SQL 2000
> George Collins
> WITH MEMBER [INV History].[INV HIST Selling Price] as
> 'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
> SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
> COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
> Price])
> AS [Min], MAX([INV HIST Selling Price]) AS [Max],
> AVG([INV HIST Selling Price]) AS [Avg]
> FROM [INV History]
> WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00',
102))
> GROUP BY [INV HIST Cust Name]
> ORDER BY SUM([INV HIST Selling Price]) DESC
>|||The following is copied from the SQL 2000 Books online. "WITH" is part of
the example.
Am I missing something?
Thanks for you help
George
Topic last updated -- July 2003
Returns the median value of a numeric expression evaluated over a set.
Syntax
Median(«Set»[, «Numeric Expression»])
Remarks
The Median function returns the median value of a numeric expression that is
specified in «Numeric Expression» and evaluated over a set specified in
«Set». The median value is the middle value in a set of ordered numbers
(unlike the mean value, which is the sum of a set of numbers divided by the
count of numbers in the set). The median value is determined by choosing the
smallest value such that at least half of the values in the set are no
greater than the chosen value. If the number of values within the set is
odd, the median value corresponds to a single value. If the number of values
within the set is even, the median value corresponds to the sum of the two
middle values divided by two.
Example
The following example, a calculated member that is executed against the
Sales cube of the FoodMart 2000 database, returns the median value of the
Unit Sales measure for the children of the Juice member in the Product
dimension:
WITH MEMBER [Measures].[MedianJuiceUnitSales] AS
'MEDIAN(Product.Juice.CHILDREN, Measures.[Unit Sales])'
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:Oeti76luEHA.3152@.TK2MSFTNGP14.phx.gbl...
> Joe Celko always writes standard ANSI SQL. Existing products more or less
> comply to the standard. In version 2000, T-SQL language used by SQL Server
> does not support WITH clause yet. Check how to calculate the median in
> T-SQL
> at http://www.aspfaq.com/show.asp?id=2506.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "george collins" <george@.nospan.com> wrote in message
> news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
>> Can someone tell me where I am blowing it, I get
>> Server: Msg 156, Level 15, State 1, Line 1
>> Incorrect syntax near the keyword 'WITH'.
>> I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
>> head. I know I am over looking the OBVIOUS, but it is not obvious to me.
>> I am trying to get the min, max, avg and MEDIAN of sales grouped by
>> customer, dollars in decending order.
>> Without the WITH MEMBER, the statement geives me min, max and avg
> perfectly,
>> HELP what am i missing out on here.
>> Thanks so much for you insight.
>> SQL 2000
>> George Collins
>> WITH MEMBER [INV History].[INV HIST Selling Price] as
>> 'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
>> SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Price,
>> COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIST Selling
>> Price])
>> AS [Min], MAX([INV HIST Selling Price]) AS [Max],
>> AVG([INV HIST Selling Price]) AS [Avg]
>> FROM [INV History]
>> WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00',
> 102))
>> GROUP BY [INV HIST Cust Name]
>> ORDER BY SUM([INV HIST Selling Price]) DESC
>>
>
begin 666 update_topic.gif
M1TE&.#EA$@.`6`/<`````````A ``_P!"0@."$A #_`$*$A(0`A(2$`(2$A(2$
M_\;&QO\``/__`/______________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M____________________________________________________________
M_____________________RP`````$@.`6```(? `="!188*#!@.PX*$ARH$.'"
MA \=,H384"+#!046:-RHD6&!CQ@.3?E2XP$')@.P4K"CQYDF!(E0IBQEP8$B+"
M!0ILAI3)<V7.@.34EXC1XDJ=,GT0M(@.4JT.A,DS]7*H6:U('3GT.9*LTJU:K3
3I5TM<C4Y=2S'LQRC>KUJ-" `.P``
`
end|||> Am I missing something?
Yes - this is part of MDX language, the language for browsing OLAP cubes in
Analysis Services, not par of the T-SQL language.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Okay, that answers the question. So I need to study, learn how to and make
a cube before this will work, or am I 100% out of the ball park.
I was trying really hard to make it work.
Thanks for the direction and information.
Always something new to learn.
George
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ea%23El6quEHA.2196@.TK2MSFTNGP14.phx.gbl...
>> Am I missing something?
> Yes - this is part of MDX language, the language for browsing OLAP cubes
> in
> Analysis Services, not par of the T-SQL language.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

median again

Can someone tell me where I am blowing it, I get
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
head. I know I am over looking the OBVIOUS, but it is not obvious to me.
I am trying to get the min, max, avg and MEDIAN of sales grouped by
customer, dollars in decending order.
Without the WITH MEMBER, the statement geives me min, max and avg perfectly,
HELP what am i missing out on here.
Thanks so much for you insight.
SQL 2000
George Collins
WITH MEMBER [INV History].[INV HIST Selling Price] as
'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS Pr
ice,
COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV HIS
T Selling
Price])
AS [Min], MAX([INV HIST Selling Price]) AS [Max],
AVG([INV HIST Selling Price]) AS [Avg]
FROM [INV History]
WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00', 10
2))
GROUP BY [INV HIST Cust Name]
ORDER BY SUM([INV HIST Selling Price]) DESCJoe Celko always writes standard ANSI SQL. Existing products more or less
comply to the standard. In version 2000, T-SQL language used by SQL Server
does not support WITH clause yet. Check how to calculate the median in T-SQL
at http://www.aspfaq.com/show.asp?id=2506.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"george collins" <george@.nospan.com> wrote in message
news:uaT0yZkuEHA.3840@.tk2msftngp13.phx.gbl...
> Can someone tell me where I am blowing it, I get
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'WITH'.
> I have ordered the book, SQL FOR SMARTIE" but I think it is way over my
> head. I know I am over looking the OBVIOUS, but it is not obvious to me.
> I am trying to get the min, max, avg and MEDIAN of sales grouped by
> customer, dollars in decending order.
> Without the WITH MEMBER, the statement geives me min, max and avg
perfectly,
> HELP what am i missing out on here.
> Thanks so much for you insight.
> SQL 2000
> George Collins
> WITH MEMBER [INV History].[INV HIST Selling Price] as
> 'MEDIAN(SellingPrice,[INV History].[INV HIST Selling Price])'
> SELECT [INV HIST Cust Name], SUM([INV HIST Selling Price]) AS
Price,
> COUNT([INV HIST Document No]) AS [No of Documents], MIN([INV H
IST Selling
> Price])
> AS [Min], MAX([INV HIST Selling Price]) AS &
#91;Max],
> AVG([INV HIST Selling Price]) AS [Avg]
> FROM [INV History]
> WHERE ([INV HIST Date] > CONVERT(DATETIME, '2003-01-01 00:00:00',
102))
> GROUP BY [INV HIST Cust Name]
> ORDER BY SUM([INV HIST Selling Price]) DESC
>|||> Am I missing something?
Yes - this is part of MDX language, the language for browsing OLAP cubes in
Analysis Services, not par of the T-SQL language.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Okay, that answers the question. So I need to study, learn how to and make
a cube before this will work, or am I 100% out of the ball park.
I was trying really hard to make it work.
Thanks for the direction and information.
Always something new to learn.
George
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ea%23El6quEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Yes - this is part of MDX language, the language for browsing OLAP cubes
> in
> Analysis Services, not par of the T-SQL language.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Median

How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.
From Northwind:
select
Median = avg (Quantity)
from
(
select
Quantity = min (Quantity) -- first value ...
from
(
select top 50 percent -- ... in upper half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity desc
) x
union all
select
Quantity = max (Quantity) -- last value ...
from
(
select top 50 percent -- ... in lower half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity asc
) x
) y
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O1vtO0OrFHA.1984@.tk2msftngp13.phx.gbl...
How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.

Median

How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.From Northwind:
select
Median = avg (Quantity)
from
(
select
Quantity = min (Quantity) -- first value ...
from
(
select top 50 percent -- ... in upper half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity desc
) x
union all
select
Quantity = max (Quantity) -- last value ...
from
(
select top 50 percent -- ... in lower half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity asc
) x
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:O1vtO0OrFHA.1984@.tk2msftngp13.phx.gbl...
How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.

Median

How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.From Northwind:
select
Median = avg (Quantity)
from
(
select
Quantity = min (Quantity) -- first value ...
from
(
select top 50 percent -- ... in upper half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity desc
) x
union all
select
Quantity = max (Quantity) -- last value ...
from
(
select top 50 percent -- ... in lower half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity asc
) x
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O1vtO0OrFHA.1984@.tk2msftngp13.phx.gbl...
How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.sql