Calculating the median with where clause condition

I am trying to calculate the median stay and average total spent (Room_Spend Food_Spend) from the below table in sqlite -

CREATE TABLE test (
    Stay Int,
    Residence Text,
    Purpose TEXT,
    Room_Spend INT,
    Food_Spend INT);
    
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (10, 'Italy', 'Business', 5, 5);   
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (2, 'Italy', 'Leisure', 0, 0);  
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (5, 'Italy', 'Leisure', 0, 0);  
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (10, 'Germany', 'Business', 0, 0);  
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (3, 'Germany', 'Business', 1, 1);
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (5, 'Germany', 'Business', 1, 1);

I am new to sql and this is what I have:

SELECT AVG(Stay)
FROM (SELECT stay, Residence
      FROM test
      ORDER BY stay
      LIMIT 2 - (SELECT COUNT(*) FROM test) % 2    -- odd 1, even 2
      OFFSET (SELECT (COUNT(*) - 1) / 2))

Any help is much appreaciated!

CodePudding user response：

One approach uses analytic functions:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Stay) rn,
              COUNT(*) OVER () AS cnt,
              AVG(Room_Spend   Food_Spend) OVER () AS total_spent
    FROM test
)

SELECT AVG(Stay) AS Stay, MAX(total_spent) AS total_spent
FROM cte
WHERE rn = (cnt / 2)   1 AND cnt % 2 = 1 OR
      rn IN (cnt / 2, cnt / 2   1) AND cnt % 2 = 0;