0

I want to compare the total number of hotel rooms that are available (for all future dates) as at now versus what was available at historic dates. The below query will retrieve the number of rooms available as at today

SELECT
date, sum(availability)
FROM
AVAILABILITY
WHERE date = current_date

The output I am looking for would look something like:

date, total number rooms available

05/07, 100

06/07, 110

today, 90

Thanks in advance

Kealan Kelly
  • 1

1 Answers1

0

Do you just want group by?

SELECT date, sum(availability)
FROM AVAILABILITY
WHERE date >= current_date
GROUP BY date;

You seem to want window functions with aggregation:

SELECT date, SUM(SUM(availability)) OVER (date DESC)
FROM AVAILABILITY
WHERE date >= current_date
GROUP BY date;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • No that will just give me availability for each particular day. What I'm looking for is total availability for all dates in the future (by day if that makes sense): For example yesterday we had 100 rooms available over the next 3 months. Tomorrow we might have 110 rooms available over the next 3 months – Kealan Kelly Jul 08 '20 at 03:24
  • The data is all in the AVAILABILITY TABLE, and relevant columns are IDAVAILABILITY (primary), PROPERTY, DATE, ROOMS UNSOLD (i.e. available) – Kealan Kelly Jul 08 '20 at 03:38