Median Calc

I'm trying to use the following to calculate the median by year for the fiel
d
"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]
CraigThere 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 XXXXX 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.examnotes.net ***|||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 fie
ld
>"labor_rate". This should calculate the median for any year where the coun
t
>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]
>
>|||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:
>
>|||...it's the last "From" line, FYI..
--
Craig
"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,
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:

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