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
>