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
>