Question

*Note: someone else answered the question but queried for the average. I need the median NOT the average. Thanks.

Hi, please help me create the following SQL query:

Create Table A with 1 column and 10 rows. The rows are filled with numbers : 1,2,3,4,5,6,7,8,9,10.

Create Table B with 1 column and 9 rows. The rows are filled with numbers: 1,2,3,4,5,6,7,8,9.

Write a single query that returns the median value of the 10 rows of TableA.

The same query should also work for Table B and return the median value of the 9 rows in Table B.

Expected Answers:

Median for Table A: 5.5

Median for Table B: 5

It would be really helpful if you could include screenshots. Thanks!

Answer 1

create table A(numbers Integer);
insert into A(numbers) values(1);

insert into A(numbers) values(2);

insert into A(numbers) values(3);

insert into A(numbers) values(4);

insert into A(numbers) values(5);

insert into A(numbers) values(6);

insert into A(numbers) values(7);

insert into A(numbers) values(8);

insert into A(numbers) values(9);

insert into A(numbers) values(10);

create table B(numbers Integer);

insert into B(numbers) values(1);

insert into B(numbers) values(2);

insert into B(numbers) values(3);

insert into B(numbers) values(4);

insert into B(numbers) values(5);

insert into B(numbers) values(6);

insert into B(numbers) values(7);

insert into B(numbers) values(8);

insert into B(numbers) values(9);

/*sql query for medain of table A*/

SELECT numbers as Median FROM
(SELECT a1.numbers, COUNT(a1.numbers) Rank
FROM A a1,A a2
WHERE a1.numbers< a2.numbers OR (a1.numbers=a2.numbers)
group by a1.numbers
order by a1.numbers desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM A);


/*sql query for medain of table B*/

SELECT numbers as Median FROM
(SELECT b1.numbers, COUNT(b1.numbers) Rank
FROM B b1,B b2
WHERE b1.numbers< b2.numbers OR (b1.numbers=b2.numbers)
group by b1.numbers
order by b1.numbers desc) b3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM B);