PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – find median price from table

I get the following error when executing an sql query and can’t figure out what I’m doing wrong, please help me. The purpose of the query is to calculate the median cost of orders for all tables – even

Text error:
"No operator matches the given name and argument types. You might need to add explicit type casts."

columns:
rn – result function row_number
price – values price

sql text:

SELECT CASE 
    WHEN (SELECT MAX(rn) FROM even) % 2 = 0 
        THEN (
            (SELECT price FROM even WHERE rn = (SELECT MAX(rn) FROM even)::decimal // 2)
            +
            (SELECT price FROM even WHERE rn = (SELECT MAX(rn) FROM even)::decimal // 2 + 1)
        ) / 2
    ELSE 
        (SELECT price FROM even WHERE rn = (SELECT MAX(rn) FROM even)::decimal // 2 + 1) 
END AS median_price
FROM even

I tried changing all the data types.

Tags:

2

Answers


  1. 0

    The SQL query you provided is likely erroring due to the use of // for integer division, which is not standard SQL syntax. Instead, you should use the / operator and handle the integer division using the FLOOR or CEIL functions.

    SELECT CASE 
WHEN (SELECT MAX(rn) FROM even) % 2 = 0 
    THEN (
        (SELECT price FROM even WHERE rn = FLOOR((SELECT MAX(rn) FROM even) / 2))
        +
        (SELECT price FROM even WHERE rn = FLOOR((SELECT MAX(rn) FROM even) / 2) + 1)
    ) / 2.0
ELSE 
    (SELECT price FROM even WHERE rn = FLOOR((SELECT MAX(rn) FROM even) / 2) + 1) END AS median_price FROM even
    Login or Signup to reply.
  2. 0

    It is because calumn you are selcting is not corrent type to perform operations like MAX. use CAST operator rather than directly using column name like

    CAST(column_name AS UNSIGNED)

    This will convert column value into integer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search