selecting a max function value and it's corresponding id value from a subquery-CodePudding

The task is to get the result of the highest number of bookings and its corresponding facility id. I thought to extract it from a subquery that is grouped by facility id and has a sum aggregate function. Then in the outer query, I will use the max function. But it doesn't work. It is printing all the facility and their sum of bookings. What is the problem?

select
    facid,
    max(total)
from
    (
        select
            facid,
            sum(slots) as total
        from
            cd.bookings
        group by
            facid
    ) as result
group by
    facid;

CodePudding user response：

The problem with your query is, You're finding SUM first and MAX of that SUM by grouping by FACILITYID which will print MAX for each facilityid.

Instead, you should skip GROUP BY and get the facilityID by summing in outer query. Here is how you can do it:

#Query 1 (using nested subquery):

First we sum the slots for each facilityid, then we find max (overall), then we add a condition HAVING SUM=MAX

/* Using nested sub-queries */

select facilityid, sum(slot) as total
from booking
group by facilityID
having sum(slot) = 
 (
  select max(total) 
  from
  (
    select facilityID, sum(slot) as total
    from booking
    group by facilityID
  ) as sqsum
 )

#Query 2 (Using RANK):

Here, we simply find the sum and rank them in DESCENDING, then we filter with RANK=1:

/* Using RANK */

select facilityID, total
from(
SELECT 
   facilityID,
   sum(slot) as total, 
   rank() OVER (ORDER BY sum(slot) DESC) AS rank
 from booking
 group by facilityID
 ) as rnk
 WHERE rank=1

Note- In both scenarios, more than 1 facilityid might be displayed if number of slots is same.

Sample results here - Check with all scenarios by updating input table

CodePudding user response：

An alternative approach is to use a Join;

WITH fac_booking AS (
SELECT facid, SUM(slot) AS factotal
FROM bookings
group by facid
),
max_booking AS (
SELECT MAX(factotal) AS maxtotal
FROM fac_booking
)
SELECT facid, factotal AS total
FROM fac_booking f
JOIN max_booking m ON f.factotal = m.maxtotal;

See Demo