Median Calc

6 Jan 2006 1:21 AM

Craig

I'm trying to use the following to calculate the median by year for the field
"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]

--
Craig

6 Jan 2006 1:48 AM

--CELKO--

There 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 bitch 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.developersdex.com ***

6 Jan 2006 2:07 AM

Steve Kass

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:

Show quote

>I'm trying to use the following to calculate the median by year for the field
>"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]
>
>
>

6 Jan 2006 4:08 AM

Craig

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

Show quote

"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:
>
> >I'm trying to use the following to calculate the median by year for the field
> >"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]
> >
> >
> >
>

6 Jan 2006 4:11 AM

Craig

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

--
Craig

Show quote

"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,
> >
> > 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 field
> > >"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]
> > >
> > >
> > >
> >

6 Jan 2006 4:03 PM

Steve Kass

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:

Show quote

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