SQL Combining MAX and SUM

Question

So I have one SQL table that contains the below columns:

License# - int
Name - char(255)
ActivityDate - datetime
FundsIn - money,null

There are ~2800 unique License numbers and will have numerous FundsIn each day. I am trying to find the last date there was activity (ActivityDate) and the sum of all of the FundsIn on that MAX ActivityDate.

Below is a query I've been trying to modify to get it to work, but it is returning the sum of the license number across all dates and not just the MAX date.

Select License, Name, MAX(ActivityDate), FundsIn
From (
    Select License, Name, ActivityDate, SUM(FundsIn) as Funds_In
    From Table1
    group by License, Name, ActivityDate
) foo
group by License, Name, FundsIn

I know the issue with the current query is that it is grouping the entire dataset of FundsIn, but I can't figure out how to limit the sum of FundsIn to only the MAX date. Any help would be appreciated.

Sample Date

License   Name   ActivityDate   FundsIn
123       A      8/29/2020      40
123       A      8/29/2020      60
123       A      8/29/2020      80
123       A      8/29/2020      55
123       A      8/30/2020      10
123       A      8/30/2020      15
123       A      8/30/2020      12
123       A      8/30/2020      60
123       A      8/30/2020      70
234       B      8/29/2020      12
234       B      8/29/2020      15
234       B      8/29/2020      19
234       B      8/29/2020      22
234       B      8/29/2020      33
234       B      8/30/2020      13
234       B      8/30/2020      78
234       B      8/30/2020      28
234       B      8/30/2020      34
234       B      8/30/2020      46

In the above data the query would return the below

License   Name   ActivityDate   FundsIn
123       A      8/30/2020      167
234       B      8/30/2020      199

Answer 1

I understand that you want the sum of the funds on the latest activity date per license (not all licenses might be active every day).

There are multiple ways to phrase this. One method is a filter with a correlated subquery:

select license, name, activityDate, sum(fundsIn) fundsIn
from mytable t
where t.activityDate = (select max(t1.activityDate) from mytable t1 where t1.license = t.license)
group by license, name, activityDate

Out of other options, a more exotic alternative is order by and top (1) with ties:

select top (1) with ties license, name, activityDate, sum(fundsIn) fundsIn
from mytable t
group by license, name, activityDate
order by row_number() over(partition by license order by activityDate desc) 

I would expect the second method to be less efficient on a large dataset.