Hey there! Let's break down how to solve your two requirements in SQLite, since you're just starting out with SQL. We'll fix up the median calculation first, then add the average total spend query.

1. Calculating Median Stay Duration #

Your existing code is on the right track, but we can clean it up and make it clearer. SQLite doesn't have a built-in MEDIAN() function, so we need to implement the logic manually:

• Sort the Stay values
• For an odd number of rows: pick the middle value
• For an even number of rows: average the two middle values

Here's the revised and complete query for median stay:

SELECT AVG(stay) AS Median_Stay
FROM (
    SELECT stay
    FROM test
    ORDER BY stay
    LIMIT 2 - (SELECT COUNT(*) FROM test) % 2  -- Returns 1 if odd row count, 2 if even
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM test)  -- Skips rows to reach the middle position
);

How this works: #

• The inner subquery sorts all Stay values and selects either 1 or 2 rows starting from the middle.
• (SELECT COUNT(*) FROM test) % 2 checks if the total number of rows is odd (remainder 1) or even (remainder 0). 2 - remainder gives us 1 for odd counts, 2 for even.
• (COUNT(*) - 1) / 2 calculates how many rows to skip to reach the middle. For your 6-row dataset, this is (6-1)/2 = 2 (integer division), so we skip the first 2 rows and select the next 2 (values 5 and 5), then average them to get 5 as the median.

2. Calculating Average Total Spend #

This is straightforward: we sum Room_Spend and Food_Spend for each row, then take the average of those sums.

Here's the query:

SELECT AVG(Room_Spend + Food_Spend) AS Average_Total_Spend
FROM test;

How this works: #

• Room_Spend + Food_Spend calculates the total spend per guest.
• AVG() takes the average of all these total values. For your dataset, the total spends are 10, 0, 0, 0, 2, 2 — the average is 14/6 ≈ 2.333.

Combine Both Results (Optional) #

If you want both statistics in a single result set, you can use UNION ALL:

SELECT 'Median Stay' AS Statistic, AVG(stay) AS Value
FROM (
    SELECT stay
    FROM test
    ORDER BY stay
    LIMIT 2 - (SELECT COUNT(*) FROM test) % 2
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM test)
)
UNION ALL
SELECT 'Average Total Spend' AS Statistic, AVG(Room_Spend + Food_Spend) AS Value
FROM test;

This will return a clean table like:

内容的提问来源于stack exchange，提问作者Filippo Sebastio