how to query latest record and aggregate results

Question

I have a following Table

Table name: REC

Name | Hits | Month | State    
Joe  | 15   | 1     | CA    
Joe  | 16   | 2     | CA    
Joe  | 12   | 3     | TX

What I want to get is a result on one line with the Name, sum of all Hits, and only the most recent State.

So my result should be:

Name | SumHits | RecentState    
Joe  | 43      | TX

---

here's what I have so far:

This query gets me the name and sum of hits:

select Name, sum(Hits)
from REC
Group By Name

This query gets me close to what I want, but it only returns me the hits in the latest month (12) instead of what I want (43)

select a.Name, a.Hits, b.Month, a.State
from REC as a
Inner Join (
    select Name, max(Month) as Month
    from REC
    Group By Name) as b
On a.Name = b.Name
And a.Month = b.Month

Answer 1

You can include your sum in your subquery, like so:

select a.Name, b.Total_Hits, b.Month, a.State
from REC as a
Inner Join (
    select Name, max(Month) as Month, sum(Hits) as Total_Hits
    from REC
    Group By Name) as b
On a.Name = b.Name
And a.Month = b.Month