Cannot perform an aggregate function on an expression containing an aggregate or a subquery. max of sum sql

Question

I have a view which looks like this view_1:

id      Office              Begin_dt    Last_dt    Days
1       Office1            2019-09-02   2019-09-08  6   
1       Office2            2019-09-09   2019-09-30  21
1       Office1            2019-10-01   2019-10-31  30  
5       Office3            2017-10-01   2017-10-16  15  
5       Office2            2017-10-17   2017-10-30  13
5       Office2            2017-11-01   2017-11-31  30  

I want to find the office where employee stayed for max time.

Expected output

id      Office             Days
1       Office1            36   
5       Office2            43

So id 1 spends 6 and 30, overall 36 days in office 1. Max time is spent in office 1 by him.

id 5 spends 13 and 30 , 43 days in office. Max time is spent in office 2

Tried so far

select id, max(sum(Days)), Office from view_1
group by id

error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Answer 1

You can use window functions and aggregation in the subquery:

select v.*
from (select v.id, v.office, sum(days) as days,
             row_number() over (partition by id order by sum(days) desc) as seqnum
      from view_1 v
      group by id, office
     ) v
where seqnum = 1;

Note that if there is a tie for the most time for an id, then this chooses an arbitrary maximum row (and that might change between runs).

Answer 2

The error is telling you the problem. You need to use a subquery or CTE.

Subquery:

SELECT id,
       MAX(DaysSums) AS MaxDaysSum,
       office
FROM (SELECT id,
             SUM(Days) AS DaysSum,
             office
      FROM view_1
      GROUP BY id,
               office) V
GROUP BY id,
         office;

CTE:

WITH Sums AS
    (SELECT id,
            SUM(Days) AS DaysSum,
            office
     FROM view_1
     GROUP BY id,
              office)
SELECT id,
       MAX(DaysSum) AS DaysSumMax,
       office
FROM Sums
GROUP BY id, office;