|
|
|
date: Mon, 28 Jul 2008 11:07:49 -0400,
group: microsoft.public.sqlserver.newusers
back
returning max, min, median, mean in one query?
Hi: I have a table that I want to return the max, min, median and mean
values from. I have the first three returned in a single query (select
Max(val) As myMax, etc.), and I have the mean returned in a separate query:
with positions
as(
select (1 + Count(*)) / 2 as mid,
1-(count(*) % 2) as even
from medprocedures where treatment = @procedure
),
rows
as
(
select treatment_duration,
row_number() over (order by treatment_duration) as rn
from medprocedures where treatment = @procedure
)
select AVG(treatment_duration) from rows JOIN positions on
rn in (positions.mid, positions.mid + positions.even)
Is it possible to include the second query in the first? I haven't been able
to work out the syntax to do this. TIA!
date: Mon, 28 Jul 2008 11:07:49 -0400
author: geekyguy
Re: returning max, min, median, mean in one query?
One way,
with positions
as(
select Count(*) As Cnt
from medprocedures where treatment = @procedure
),
orderedrows
as
(
select treatment_duration,
row_number() over (order by treatment_duration) as rn
from medprocedures where treatment = @procedure
)
Select Min(treatment_duration) As 'Min',
Avg(treatment_duration) As 'Avg',
Max(treatment_duration) As 'Max',
Avg(Case When RowNum In ((Cnt + 1) / 2, (Cnt + 2) / 2) Then
treatment_duration Else Null End) As 'Median'
From orderedrows
Cross Join positions
That will give you a warning, "Null value is eliminated by an aggregate or
other SET operation." which you can ignore.
BTW, you should avoid using reserved words (like rows) as cte names, so I
changed the name of the second cte to orderedrows.
Tom
"geekyguy" wrote in message
news:ODt%23zOM8IHA.3696@TK2MSFTNGP04.phx.gbl...
> Hi: I have a table that I want to return the max, min, median and mean
> values from. I have the first three returned in a single query (select
> Max(val) As myMax, etc.), and I have the mean returned in a separate
> query:
>
> with positions
> as(
> select (1 + Count(*)) / 2 as mid,
> 1-(count(*) % 2) as even
> from medprocedures where treatment = @procedure
> ),
> rows
> as
> (
> select treatment_duration,
> row_number() over (order by treatment_duration) as rn
> from medprocedures where treatment = @procedure
> )
> select AVG(treatment_duration) from rows JOIN positions on
> rn in (positions.mid, positions.mid + positions.even)
>
> Is it possible to include the second query in the first? I haven't been
> able to work out the syntax to do this. TIA!
date: Mon, 28 Jul 2008 12:14:33 -0400
author: Tom Cooper
Re: returning max, min, median, mean in one query?
My approach is a bit different than yours but I would do it this way:
-- Given this table
CREATE TABLE Table1
(Col1 int)
INSERT Table1 VALUES (5)
INSERT Table1 VALUES (4)
INSERT Table1 VALUES (8)
INSERT Table1 VALUES (7)
INSERT Table1 VALUES (2)
INSERT Table1 VALUES (12)
INSERT Table1 VALUES (4)
GO
-- Your results would be
SELECT
MAX(Maximum) AS Maximum,
MAX(Minimum) AS Minimum,
MAX(Mean) AS Mean,
MAX(Median) AS Median
FROM
(SELECT
MAX(Col1) AS Maximum,
MIN(Col1) AS Minimum,
AVG(Col1) AS Mean,
0 AS Median
FROM Table1
UNION
SELECT 0, 0, 0, MAX(Col1) AS Median
FROM
(SELECT TOP 50 PERCENT Col1
FROM Table1
ORDER BY Col1) AS Subtable ) AS UnionTable
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.
"geekyguy" wrote in message
news:ODt%23zOM8IHA.3696@TK2MSFTNGP04.phx.gbl...
> Hi: I have a table that I want to return the max, min, median and mean
> values from. I have the first three returned in a single query (select
> Max(val) As myMax, etc.), and I have the mean returned in a separate
> query:
>
> with positions
> as(
> select (1 + Count(*)) / 2 as mid,
> 1-(count(*) % 2) as even
> from medprocedures where treatment = @procedure
> ),
> rows
> as
> (
> select treatment_duration,
> row_number() over (order by treatment_duration) as rn
> from medprocedures where treatment = @procedure
> )
> select AVG(treatment_duration) from rows JOIN positions on
> rn in (positions.mid, positions.mid + positions.even)
>
> Is it possible to include the second query in the first? I haven't been
> able to work out the syntax to do this. TIA!
date: Mon, 28 Jul 2008 10:22:31 -0700
author: Rick Byham, \(MSFT\)
Re: returning max, min, median, mean in one query?
That doesn't quite give what the OP wanted. It works if there is an odd
number number of rows in the table, but not if there is an even number of
rows. That is, if the table was
INSERT #Table1 VALUES (5)
INSERT #Table1 VALUES (4)
INSERT #Table1 VALUES (8)
INSERT #Table1 VALUES (7)
INSERT #Table1 VALUES (2)
INSERT #Table1 VALUES (12)
The OP was computing the median as 6 (the mean of the middle two values 5
and 7). Your query would return 6.
Tom
"Rick Byham, (MSFT)" wrote in message
news:uvywFaN8IHA.4928@TK2MSFTNGP05.phx.gbl...
> My approach is a bit different than yours but I would do it this way:
> -- Given this table
> CREATE TABLE Table1
> (Col1 int)
> INSERT Table1 VALUES (5)
> INSERT Table1 VALUES (4)
> INSERT Table1 VALUES (8)
> INSERT Table1 VALUES (7)
> INSERT Table1 VALUES (2)
> INSERT Table1 VALUES (12)
> INSERT Table1 VALUES (4)
> GO
> -- Your results would be
> SELECT
> MAX(Maximum) AS Maximum,
> MAX(Minimum) AS Minimum,
> MAX(Mean) AS Mean,
> MAX(Median) AS Median
> FROM
> (SELECT
> MAX(Col1) AS Maximum,
> MIN(Col1) AS Minimum,
> AVG(Col1) AS Mean,
> 0 AS Median
> FROM Table1
> UNION
> SELECT 0, 0, 0, MAX(Col1) AS Median
> FROM
> (SELECT TOP 50 PERCENT Col1
> FROM Table1
> ORDER BY Col1) AS Subtable ) AS UnionTable
>
> --
> Rick Byham (MSFT), SQL Server Books Online
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "geekyguy" wrote in message
> news:ODt%23zOM8IHA.3696@TK2MSFTNGP04.phx.gbl...
>> Hi: I have a table that I want to return the max, min, median and mean
>> values from. I have the first three returned in a single query (select
>> Max(val) As myMax, etc.), and I have the mean returned in a separate
>> query:
>>
>> with positions
>> as(
>> select (1 + Count(*)) / 2 as mid,
>> 1-(count(*) % 2) as even
>> from medprocedures where treatment = @procedure
>> ),
>> rows
>> as
>> (
>> select treatment_duration,
>> row_number() over (order by treatment_duration) as rn
>> from medprocedures where treatment = @procedure
>> )
>> select AVG(treatment_duration) from rows JOIN positions on
>> rn in (positions.mid, positions.mid + positions.even)
>>
>> Is it possible to include the second query in the first? I haven't been
>> able to work out the syntax to do this. TIA!
>
date: Mon, 28 Jul 2008 14:21:40 -0400
author: Tom Cooper
Re: returning max, min, median, mean in one query?
Oops! I meant your query returns 5.
Tom
"Tom Cooper" wrote in message
news:OUEiF7N8IHA.2064@TK2MSFTNGP02.phx.gbl...
> That doesn't quite give what the OP wanted. It works if there is an odd
> number number of rows in the table, but not if there is an even number of
> rows. That is, if the table was
>
> INSERT #Table1 VALUES (5)
> INSERT #Table1 VALUES (4)
> INSERT #Table1 VALUES (8)
> INSERT #Table1 VALUES (7)
> INSERT #Table1 VALUES (2)
> INSERT #Table1 VALUES (12)
>
> The OP was computing the median as 6 (the mean of the middle two values 5
> and 7). Your query would return 6.
>
> Tom
>
> "Rick Byham, (MSFT)" wrote in message
> news:uvywFaN8IHA.4928@TK2MSFTNGP05.phx.gbl...
>> My approach is a bit different than yours but I would do it this way:
>> -- Given this table
>> CREATE TABLE Table1
>> (Col1 int)
>> INSERT Table1 VALUES (5)
>> INSERT Table1 VALUES (4)
>> INSERT Table1 VALUES (8)
>> INSERT Table1 VALUES (7)
>> INSERT Table1 VALUES (2)
>> INSERT Table1 VALUES (12)
>> INSERT Table1 VALUES (4)
>> GO
>> -- Your results would be
>> SELECT
>> MAX(Maximum) AS Maximum,
>> MAX(Minimum) AS Minimum,
>> MAX(Mean) AS Mean,
>> MAX(Median) AS Median
>> FROM
>> (SELECT
>> MAX(Col1) AS Maximum,
>> MIN(Col1) AS Minimum,
>> AVG(Col1) AS Mean,
>> 0 AS Median
>> FROM Table1
>> UNION
>> SELECT 0, 0, 0, MAX(Col1) AS Median
>> FROM
>> (SELECT TOP 50 PERCENT Col1
>> FROM Table1
>> ORDER BY Col1) AS Subtable ) AS UnionTable
>>
>> --
>> Rick Byham (MSFT), SQL Server Books Online
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "geekyguy" wrote in message
>> news:ODt%23zOM8IHA.3696@TK2MSFTNGP04.phx.gbl...
>>> Hi: I have a table that I want to return the max, min, median and mean
>>> values from. I have the first three returned in a single query (select
>>> Max(val) As myMax, etc.), and I have the mean returned in a separate
>>> query:
>>>
>>> with positions
>>> as(
>>> select (1 + Count(*)) / 2 as mid,
>>> 1-(count(*) % 2) as even
>>> from medprocedures where treatment = @procedure
>>> ),
>>> rows
>>> as
>>> (
>>> select treatment_duration,
>>> row_number() over (order by treatment_duration) as rn
>>> from medprocedures where treatment = @procedure
>>> )
>>> select AVG(treatment_duration) from rows JOIN positions on
>>> rn in (positions.mid, positions.mid + positions.even)
>>>
>>> Is it possible to include the second query in the first? I haven't been
>>> able to work out the syntax to do this. TIA!
>>
>
>
date: Mon, 28 Jul 2008 14:30:03 -0400
author: Tom Cooper
|
|
