I am trying to calculate the total booking number and the percentage for each hotel per year using POSTGRESQL. Here is my code:
WITH distribution_per_year AS
(
SELECT hotel, arrival_date_year,
COUNT(*) AS booking_by_hotel,
(SELECT COUNT(*) AS total_booking FROM "Full_Data" )
FROM "Full_Data"
GROUP BY hotel, arrival_date_year
)
SELECT hotel, arrival_date_year, booking_by_hotel, total_booking,
round(booking_by_hotel *100.00 / total_booking , 2) as percent
FROM distribution_per_year
and it worked, it gives me the results as I wanted
| hotel | arrival_date_year | booking_by_hotel | total_booking | percent |
|:------ |:----------------- |:---------------- |:------------- |:--------|
| Hotel1 | 2015 | 6526 | 100561 | 6.49 |
| Hotel1 | 2016 | 33210 | 100561 | 33.02 |
| Hotel1 | 2017 | 20064 | 100561 | 19.95 |
| Hotel2 | 2015 | 6758 | 100561 | 6.72 |
| Hotel2 | 2016 | 22434 | 100561 | 22.31 |
| Hotel2 | 2017 | 11569 | 100561 | 11.50 |
My question is: I noticed it takes time to run this code. I think it's because it repeats the subquery every time it group by
(SELECT COUNT(*) AS total_booking FROM "Full_Data" )
Is there a way to enhance this code??