Compare three columns in SQL table to get average
You can use an expression case
(cases where col [n] might be equal are not covered - instructions are unclear):
select case
when (col1 > col2 and col2 > col3) or (col3 > col2 and col2 > col1) then col2
when (col1 > col3 and col3 > col2) or (col2 > col3 and col3 > col1) then col3
when (col3 > col1 and col1 > col2) or (col2 > col1 and col1 > col3) then col1
end
from tbl
Edit
Taking into account the new terms provided by the OP:
- The values of two or three columns can be equal.
- Any two or three columns can be empty. Could you please tell me how to deal with these cases.
select case
when col2 is not null and
((col1 is null and col3 is null) or
(col3 is null and col1 >= col2) or
(col1 is null and col3 >= col2) or
(col1 > col2 and col2 >= col3) or
(col3 > col2 and col2 >= col1)) then col2
when col3 is not null and
((col1 is null and col2 is null) or
(col2 is null and col1 >= col3) or
(col1 is null and col2 >= col3) or
(col1 > col3 and col3 >= col2) or
(col2 > col3 and col3 >= col1)) then col3
when col1 is not null and
((col2 is null and col3 is null) or
(col2 is null and col3 >= col1) or
(col3 is null and col2 >= col1) or
(col3 > col1 and col1 >= col2) or
(col2 > col1 and col1 >= col3)) then col1
else 0
end
from tbl
source to share
If you are using Postgres, depending on your performance needs, a very good way to solve such problems is using a language extension. I'm familiar with Python, which is a much nicer language for expressing this kind of computation than plain SQL, so I could do something like this (after installing a language extension plpythonu
):
ely=# create or replace function getmid (a integer, b integer, c integer)
returns integer
as $$
if (a is None) or (b is None) or (c is None):
return None
return sorted([a, b, c])[1]
$$ LANGUAGE plpythonu;
CREATE FUNCTION
I don't have good toy data to show calculations on three integer columns from one table, but using some toy tables, you can see the function is used like this:
ely=# select getmid(a.actor_id, b.movie_id, 3)
from actors a
join movies_actors b
on a.actor_id = b.actor_id
limit 15;
which outputs
getmid
--------
3
3
3
4
5
6
7
8
9
10
11
12
13
14
15
(15 rows)
One of the huge advantages is that you can write a Python function to generalize by the number of columns, whereas writing this as a stored procedure is a little more painful and manually extending a huge set of case statements as the number of columns grows extremely impractical.
Similar functionality may exist in other dialects of SQL - I can only confirm how it works with Postgres.
source to share
Here's a working example for SQL Server. The same idea will work on other platforms as well. (Assumes values are non-zero.)
declare @a int = 10;
declare @b int = 214;
declare @c int = 131;
select @a + @b + @c
- (((@a + @b - abs(@a-@b)) / 2.0) + @c - abs(((@a + @b - abs(@a-@b)) / 2.0) - @c)) / 2.0
- (((@a + @b + abs(@a-@b)) / 2.0) + @c + abs(((@a + @b + abs(@a-@b)) / 2.0) - @c)) / 2.0
source to share