Hi Everyone,
I would like to do some of the things i do on excel on SQL since excel slows down-freezes frequently and i was hoping someone may be able to help me as i nearly know nothing about SQL. I've read some articles and asked AI to write me a code but it did not work so far. I have got a long list, that has the names and price info of several crypto pairs. I would like the average column to get the average of last 6 prices (based on index number). However, if there are less than 6 prices left for a particular pair, average field should be left blank. The list goes & has multiple pairs, so starting from index 1 of each pair, i would like to start getting the averages again.
AI has written me the following code but when i updated some fields, it either did not work or calculated the same average number all across the average column.
Thank you very much
-- Update the new column with the average prices
UPDATE crypto_table
SET average_price = (
SELECT CASE
WHEN COUNT(*) >= 3 THEN ROUND(AVG(price), 2)
ELSE NULL -- or an empty string ''
END
FROM (
SELECT price, ROW_NUMBER() OVER (PARTITION BY crypto_pair ORDER BY index_number DESC) AS rn
FROM crypto_table AS t2
WHERE t2.crypto_pair = crypto_table.crypto_pair
) AS sub
WHERE rn <= 3
);