SELECT hotel, SUM(stays_in_weekend_nights) AS 'total weekend night' 
FROM hotel_bookings
GROUP BY hotel;

I want to show the results from above as as a percentage rather than the numbers you see. I tried using:

SELECT
    hotel, 
    stays_in_weekend_nights * 100/ SUM(stays_in_weekend_nights) OVER() 'Percentage'

However, that only gives me the percentage of each individual booking which is not what I want. Any suggestions?

CodePudding user response：

What you've written gives the percentage of the referenced column.

If you want the percentage of the row, you have to explicitly state which columns you're adding together.

SELECT
  hotel,
  SUM(stays_in_weekend_nights) AS [total weekend night],
  SUM(stays_in_week_nights) AS [total week night],
  SUM(stays_in_weekend_nights) * 100.0 / SUM(stays_in_weekend_nights   stays_in_week_nights) AS [% weekend night],
  SUM(stays_in_week_nights) * 100.0 / SUM(stays_in_weekend_nights   stays_in_week_nights) AS [% week night]
FROM
  hotel_bookings
GROUP BY
  hotel;

You can avoid some repetition with sub-queries or common table expressions...

WITH
  totals AS
(
  SELECT
    hotel,
    SUM(stays_in_weekend_nights) AS [total weekend night],
    SUM(stays_in_week_nights)    AS [total week night]
  FROM
    hotel_bookings
  GROUP BY
    hotel
)
SELECT
  *,
  [total weekend night] * 100.0 / ([total week night]   [total weekend night])   AS [% weekend night],
  [total week night]    * 100.0 / ([total week night]   [total weekend night])   AS [% week night]
FROM
  totals

There may even be other (better) options, if you show us what the originating data looks like.