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

MDX: combining SUM and Existing

Hi,
I have problem summing values in leaf level and total level.
Lets say, I would like to to get orders in dealer price for each selected
product as well as sum for selected products only. As example, this MDX
could demonstrate my case in Adventure Works database:

with member [Dealer Price Orders] as

SUM(
Existing ([Product].[Product Categories].[Product]),
iif([Product].[Dealer Price].MemberValue = 0 , null,
[Order Quantity]*[Product].[Dealer Price].MemberValue)
)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product
Categories].[All Products]})})
ON COLUMNS
FROM (SELECT ({[Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure
Works])
WHERE ([Measures].[Dealer Price Orders])

Unfortunately, this MDX returns Dealer Price Sales for all Products, but not
for Selected [Accessories] and [Bikes]. VisualTotals function does not help
in this case. Does anyone has a solution how to rework my calculated member?

Ramunas Balukonis

on 2005 sp2, I get a blank resultset from your test query - so a lot of people probably can't use it to give you an answer.

are you on something like sp1 or ssas 2000?

|||

Unfortunately the Existing statement currently doesn't take into account subselect restrictions.

One possibility is if you can define your calculation on leaf cells, then subselect visualtotals can be achieved. Here is one example, note that I changed [Product] to [Product Name] which matches my version of Adventure Works.

with cell calculation X for '([Product].[Product Categories].[Product Name].members, [Order Quantity])' as

iif([Product].[Dealer Price].MemberValue = 0 , null,

[Order Quantity]*[Product].[Dealer Price].MemberValue)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})

ON COLUMNS

FROM (SELECT ({[Product].[Product Categories].[Category].&[4],

[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure Works])

WHERE ([Measures].[Order Quantity])

|||

Here is another way which uses calculated member instead of cell calculation. It uses the fact that query scope named sets are resolved using subselect restrictions.

with set S as [Product].[Product Categories].[Product Name].members

member [Dealer Price Orders] as

SUM(

existing(S),

iif([Product].[Dealer Price].MemberValue = 0 , null,

[Order Quantity]*[Product].[Dealer Price].MemberValue)

)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})

ON COLUMNS

FROM (SELECT ({[Product].[Product Categories].[Category].&[4],

[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure Works])

WHERE ([Measures].[Dealer Price Orders])

MDX multiple where condition

Is there any way in MDX to give multiple where clause:
Example in SQL:
SELECT * FROM Price
WHERE Type in("Cost1", "Cost2", "Cost3")
I have total 10 Cost types. But I need this query to return only 3 listed
above. It is simple with SQL.
My question is How to do this in MDX?
I have tried using filter but I am not getting correct values. Is there any
way in WHERE clause? Is there any other solution?
My Development environment is:
Reporting Services using Visual Studio.Net
OLAP: MDX Queries (Base Database is MS SQL Server)
Thanks,
SamThis question would more likely be answered in the OLAP newsgroup.
microsoft.public.sqlserver.olap
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> Is there any way in MDX to give multiple where clause:
> Example in SQL:
> SELECT * FROM Price
> WHERE Type in("Cost1", "Cost2", "Cost3")
> I have total 10 Cost types. But I need this query to return only 3 listed
> above. It is simple with SQL.
> My question is How to do this in MDX?
> I have tried using filter but I am not getting correct values. Is there
> any
> way in WHERE clause? Is there any other solution?
> My Development environment is:
> Reporting Services using Visual Studio.Net
> OLAP: MDX Queries (Base Database is MS SQL Server)
> Thanks,
> Sam|||Use named set.
with
set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
select
{ [measures] } on columns,
{ [Type Cost Set] } on rows
from cube
"Jeff A. Stucker" wrote:
> This question would more likely be answered in the OLAP newsgroup.
> microsoft.public.sqlserver.olap
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > Is there any way in MDX to give multiple where clause:
> >
> > Example in SQL:
> > SELECT * FROM Price
> > WHERE Type in("Cost1", "Cost2", "Cost3")
> >
> > I have total 10 Cost types. But I need this query to return only 3 listed
> > above. It is simple with SQL.
> > My question is How to do this in MDX?
> >
> > I have tried using filter but I am not getting correct values. Is there
> > any
> > way in WHERE clause? Is there any other solution?
> >
> > My Development environment is:
> > Reporting Services using Visual Studio.Net
> > OLAP: MDX Queries (Base Database is MS SQL Server)
> >
> > Thanks,
> > Sam
>
>|||Cost types are not sequential. It will not work with range. Is there anything
in MDX where condition:
Example: [Type].[Type1 or Type2 or Type3]
Sam
"mike" wrote:
> Use named set.
> with
> set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
> select
> { [measures] } on columns,
> { [Type Cost Set] } on rows
> from cube
> "Jeff A. Stucker" wrote:
> > This question would more likely be answered in the OLAP newsgroup.
> > microsoft.public.sqlserver.olap
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > > Is there any way in MDX to give multiple where clause:
> > >
> > > Example in SQL:
> > > SELECT * FROM Price
> > > WHERE Type in("Cost1", "Cost2", "Cost3")
> > >
> > > I have total 10 Cost types. But I need this query to return only 3 listed
> > > above. It is simple with SQL.
> > > My question is How to do this in MDX?
> > >
> > > I have tried using filter but I am not getting correct values. Is there
> > > any
> > > way in WHERE clause? Is there any other solution?
> > >
> > > My Development environment is:
> > > Reporting Services using Visual Studio.Net
> > > OLAP: MDX Queries (Base Database is MS SQL Server)
> > >
> > > Thanks,
> > > Sam
> >
> >
> >|||with
set [Type Cost Set] as '{ [Type].[Cost1], [Type].[Cost2], [Type].[Cost3] }'
or if you want you could use the filter function, but know that using filter
the whole set is return then the results filtered so you get overhead. See
BOL.
"Sam" wrote:
> Cost types are not sequential. It will not work with range. Is there anything
> in MDX where condition:
> Example: [Type].[Type1 or Type2 or Type3]
> Sam
>
> "mike" wrote:
> > Use named set.
> >
> > with
> > set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
> >
> > select
> > { [measures] } on columns,
> > { [Type Cost Set] } on rows
> > from cube
> >
> > "Jeff A. Stucker" wrote:
> >
> > > This question would more likely be answered in the OLAP newsgroup.
> > > microsoft.public.sqlserver.olap
> > >
> > > --
> > > Cheers,
> > >
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > > news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > > > Is there any way in MDX to give multiple where clause:
> > > >
> > > > Example in SQL:
> > > > SELECT * FROM Price
> > > > WHERE Type in("Cost1", "Cost2", "Cost3")
> > > >
> > > > I have total 10 Cost types. But I need this query to return only 3 listed
> > > > above. It is simple with SQL.
> > > > My question is How to do this in MDX?
> > > >
> > > > I have tried using filter but I am not getting correct values. Is there
> > > > any
> > > > way in WHERE clause? Is there any other solution?
> > > >
> > > > My Development environment is:
> > > > Reporting Services using Visual Studio.Net
> > > > OLAP: MDX Queries (Base Database is MS SQL Server)
> > > >
> > > > Thanks,
> > > > Sam
> > >
> > >
> > >|||I found a work around, instead getting row wise I am getting required data in
column axis.
Thanks for your help.
Sam
"mike" wrote:
> with
> set [Type Cost Set] as '{ [Type].[Cost1], [Type].[Cost2], [Type].[Cost3] }'
> or if you want you could use the filter function, but know that using filter
> the whole set is return then the results filtered so you get overhead. See
> BOL.
> "Sam" wrote:
> > Cost types are not sequential. It will not work with range. Is there anything
> > in MDX where condition:
> > Example: [Type].[Type1 or Type2 or Type3]
> > Sam
> >
> >
> > "mike" wrote:
> >
> > > Use named set.
> > >
> > > with
> > > set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
> > >
> > > select
> > > { [measures] } on columns,
> > > { [Type Cost Set] } on rows
> > > from cube
> > >
> > > "Jeff A. Stucker" wrote:
> > >
> > > > This question would more likely be answered in the OLAP newsgroup.
> > > > microsoft.public.sqlserver.olap
> > > >
> > > > --
> > > > Cheers,
> > > >
> > > > '(' Jeff A. Stucker
> > > > \
> > > >
> > > > Business Intelligence
> > > > www.criadvantage.com
> > > > ---
> > > > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > > > news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > > > > Is there any way in MDX to give multiple where clause:
> > > > >
> > > > > Example in SQL:
> > > > > SELECT * FROM Price
> > > > > WHERE Type in("Cost1", "Cost2", "Cost3")
> > > > >
> > > > > I have total 10 Cost types. But I need this query to return only 3 listed
> > > > > above. It is simple with SQL.
> > > > > My question is How to do this in MDX?
> > > > >
> > > > > I have tried using filter but I am not getting correct values. Is there
> > > > > any
> > > > > way in WHERE clause? Is there any other solution?
> > > > >
> > > > > My Development environment is:
> > > > > Reporting Services using Visual Studio.Net
> > > > > OLAP: MDX Queries (Base Database is MS SQL Server)
> > > > >
> > > > > Thanks,
> > > > > Sam
> > > >
> > > >
> > > >