I am trying to find median of the field "bal" based on "key" in DB2. I get this error when I execute this code "Error: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=KEY, DRIVER=3.66.46, SQLState: 42703).
Appreciate your comments !!
SELECT
key,
AVG(bal)
FROM
(
SELECT
key,
bal,
ROW_NUMBER() OVER (
PARTITION BY key
ORDER BY bal ASC) AS RowAsc,
ROW_NUMBER() OVER (
PARTITION BY key
ORDER BY bal DESC) AS RowDesc
FROM tab1
) x
WHERE
RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY key
ORDER BY key;
Ensure that every reference to key
becomes "key"
SELECT
"key"
, AVG(bal)
FROM (
SELECT
"key"
, bal
, ROW_NUMBER() OVER (PARTITION BY "key"
ORDER BY bal ASC) AS rowasc
, ROW_NUMBER() OVER (PARTITION BY "key"
ORDER BY bal DESC) AS rowdesc
FROM tab1
) x
WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY
"key"
ORDER BY
"key"
;